formation power bi

dans le query editor ,query settings fyha les modifications eli aamlhm power bi
automatiquement ybdl type de colonne o lhead yriglou
transformation
li feha barcha null nfskhouhaPower Query Editor allows users to add new data
sources, transform and format data (e.g., splitting columns), view and modify
applied query steps, and access training resources. Creating relationships between
tables is part of data modeling, not Power Query Editor.
star shema=modele en etoile
nb:ken nlka fl bar chart taey equivalent resultat yaani lzmni naaml modif f hkyt
modelisation
active/inactive relationship: ctive hya lprincipal taamlt mloul aamlt
connexion entre les tables et elle apparait ligne discontinue et lautre cest la
2eme et ligne vertical
code:
CALCULATE(
SUM(fact_InternetSales[SalesAmount]),
USERELATIONSHIP(dim_Date[DateKey], fact_InternetSales[OrderDateKey])
)
Cela calcule la somme des ventes tout en activant la relation inactive(avec la fct
userelationship) entre dim_Date[DateKey] et fact_InternetSales[OrderDateKey].
creting multiple instances of the dim date table
**n Power BI, multiple instances of relationships refer to situations where you
have more than one relationship between two tables. For example, you might have a
Date table related to a Sales table, but you could have different date columns in
the Sales table, such as:

OrderDate
ShipDate
DeliveryDate
In this case, you could create multiple relationships between the Date table and
the Sales table, one for each date column. However, only one relationship can be
active at a time. The other relationships are considered inactive.

Why Use Multiple Relationships?/lfunction khyr in real life

There are several scenarios where you may need multiple relationships between two
tables:

Different Time Dimensions: If you have different date columns, such as OrderDate,
ShipDate, and InvoiceDate, you might need separate relationships for each.
Fact Tables: In cases where you have multiple fact tables (e.g., sales, returns,
shipments), you may need to establish different relationships between your fact
tables and dimension tables.
Multiple Logical Relationships: You may have different kinds of relationships based
on business logic, such as a direct relationship between customers and sales, and
another relationship between promotions and sales.**
yaani hwaa chyaaml copie mn table date
dans la page modeling ; dans le but de faire chaque departement view his own data
we click at manage roles
(on selectionne role la table le filtre applique et puis on presse view role as the
role(customer dans cet expl) and we move to the data view o see his donnes
<<< we can set up as many roles as we want to><
partie : refreching data in power bi
using sql server(cet ordinateur)marbouta b sql server(direct query cannot show
data)
section4;hierarchies
dans notre teble we have dim date dans la visualizations we have axe x(axis) to
view the hierarchy (the date gets broken into a hierarchy/standard hiearrchy goes
in the following order(year quarter month day)
hierarchies are a feature thats available to power bi that allows users to drill
down into thei data
partie Q $ A
Inactive relationships are represented by a dotted line, not a solid one.
3. The DAX function used to create a series of values in Power BI is called
genetareseries
In Import Mode, Power BI loads a snapshot of the data, and you must manually
refresh the data to see updates. In Direct Query mode, the data is live, and
changes in the SQL database are immediately reflected in Power BI without needing
to refresh.
Even after sharing a dashboard with a user, you can still restrict access to the
underlying dataset by using Row-Level Security (RLS) in Power BI. RLS allows you to
define roles and security filters that limit the data a user can view, even if they
have access to the dashboard. For example, you can set up rules so that users only
see data relevant to their department or region, ensuring sensitive data is not
exposed, even if the dashboard itself is shared with them.
The Q&A feature in Power BI can work with multiple tables as long as those tables
are correctly related in the data model. Power BI's Q&A uses the relationships
between tables to fetch and aggregate data from different sources. For example, you
can ask a question like "Total sales by country" where "sales" comes from one table
and "country" comes from another table, provided these tables are connected through
relationships in the data model
first mini project: i have 3 tasks(cretae a modele create visuals
DAX SSIS TABULAR /TABULAR MODELINDEX=DAX
dax is not a programming language is primarily a formula language(query language)
it can be used to create custom calculations
#learning the difference between calculated columns and measures
cretaing a new column uses ur ram so use it wisely/its is recommended to do it in
one column to avoid de^leting RAM resources
measures depend on agregation of the data
Measure
Definition: A measure is a dynamic calculation that is evaluated based on user
interactions with the report, such as slicers, filters, or visuals. Measures are
not stored in the data model and are calculated on the fly when a visual is
rendered.
Scope: Aggregation-level calculation. Measures are evaluated in the context of the
report visual.
Storage: Measures are not stored in the data model, making them memory efficient.
Use Cases:
When you need calculations that depend on aggregations (like sums, averages, or
counts).
When the result depends on the filter context applied in the report (e.g., slicers,
rows, columns in a visual).
Example: Calculating total profit for all sales in a visual, respecting filters.
Calculated Column
Definition: A calculated column is a new column added to a table in your data
model. It is computed for each row in the table based on DAX (Data Analysis
Expressions) formulas and becomes part of the table, similar to any other column.
Scope: Row-level calculation. The calculation is performed for each row
independently.
Storage: Calculated columns are stored in memory (part of the data model), which
increases the file size and memory usage.
Use Cases:
When the calculation depends on row-level data and should be evaluated for each
row.
When the result needs to be reused as a dimension or a slicer/filter.
Example: Adding a column to calculate the profit per row as Profit = Sales - Cost.
#star schemas
it requires tables to be classified as dimensions or facts
dimensions are unique values and facts are repetitions of these values
fact table;Includes foreign keys linking to dimension tables for additional
context.
dim table Includes a primary key used to join with the fact table.
#partie questions
Using a DAX aggregation function like SUM() in a calculated column will create
duplicate
values across all rows, but using it in a measure will return a dynamically
calculated result.
he relationship is typically one-to-many because one value in a dimension table
(e.g., one customer) can correspond to many records in the fact table (e.g., many
sales)
#filter flow
select the main visual edit interactions and stop (if u want the visual to stop
interact baseed on other visual)
Key Differences
Feature RELATED RELATEDTABLE
Return Type Single value (scalar). Table of related rows.
Direction of Relationship From "many" side to "one" side (e.g., fact to
dimension). From "one" side to "many" side (e.g., dimension to fact).
Use Case To fetch a specific value from a related table. To retrieve and analyze
rows in a related table.
Example Function RELATED(Products[ProductName])
COUNTROWS(RELATEDTABLE(Sales))
When to Use Each
Use RELATED:

When you need a single value from a related table.

For example, adding descriptive attributes (like ProductName) from a dimension
table into a fact table.
Use RELATEDTABLE:

When you need to work with a collection of related rows.

For example, aggregating data (like counting or summing) from a related table.
Practical Example
Scenario:
You have:

Customers Table: Contains CustomerID, CustomerName.

Orders Table: Contains OrderID, CustomerID, OrderAmount.
Example Use Cases:
RELATED:

Adding CustomerName to the Orders Table:

DAX
Copier le code
RELATED(Customers[CustomerName])
RELATEDTABLE:

Calculating the total OrderAmount for each customer in the Customers Table:
DAX
Copier le code
SUMX(RELATEDTABLE(Orders), Orders[OrderAmount])
#bidiretional cross filter(many to one)
the product can filter anything that its inside our facts underscore internet sales
#function calculate
how many sales
measure=count(factsalestables) and am going to add this measure in our table
calculate looks for only the thing that we put inside the filter
ewpl:
totalsalessilver=CALCULATE(totalsales),FILTER(dim_product,dim_product[color]="silve
r")
#time intelligence functions
the time intelligence requires a good dim date table which may not always be
vailable inside ur data model
CALENDAR(<start_date>,<end_date>)
CALENDAR(<MINX(sales,date),MAXX(forecast,date)) we can use ADCOLUMNS to add a new
columns where we can see the number of the month year etc..
time intelligence also requires an agregate function(sum ,count etc..)
the total sales for the last year
ALL FUNCTION ignores any filters applied on in the current filter context
#tricks# to have our measures table on top of all the other tables

we use generateseries= it generates an empty row inside a new table

#measures=GENERATESERIES(1.1.1)
#*****
dynamic text box to adapt to changes based on user selection,the box needs to say
which country is selected and if no country is selected we should see something
along the lines "no country selected"
***NB***
la question demandee est dynamic measure for selected Currency,based on the
selected Currency the measure should return the sales amount in the selected
Currency ,if no Currency has been selected the measure should say "no Currency
selected"
how to do it ?
1.we create a slice measure for Currency alternative(feha les différents usd,eur
etc)
2.we create two new measures the first totalsalesamount=SUM(fact[salesamount])
this a simple one to calculate the total of sales
and the second one is a measure where no metter filter is applied we have to
calculate the amount in the cuurent currency selected
TotalSalesAmountALL = CALCULATE([TotalSalesAmount],ALL(dim_Currency))
La fonction ALL supprime tout filtre sur la table ou colonne spécifiée (ici,
dim_Currency).
Cela permet de comparer le total des ventes dans toutes les devises (sans
sélection) avec le total pour une devise sélectionnée. Cela est crucial pour les
analyses de comparaison.
3. the titlebar
TitleBar =
VAR CurrencySelected =
IF(
ISFILTERED(dim_Currency[CurrencyAlternateKey]),
VALUES(dim_Currency[CurrencyAlternateKey]),
BLANK()
)

RETURN
IF(
ISFILTERED(dim_Currency[CurrencyAlternateKey]),
"Sales Amount in " & CurrencySelected & " vs All Currencies",
"Please, Select Currency from the Dropdown Menu"
)
NB**La déclaration VAR en DAX (Data Analysis Expressions) est utilisée pour définir
des variables dans une mesure ou une colonne calculée
TitleBar =
VAR CurrencySelected =
IF(
ISFILTERED(dim_Currency[CurrencyAlternateKey]),
 VALUES(dim_Currency[CurrencyAlternateKey]),
BLANK()
)

RETURN
IF(
ISFILTERED(dim_Currency[CurrencyAlternateKey]),
"Sales Amount in " & CurrencySelected & " vs All Currencies",
"Please, Select Currency from the Dropdown Menu"
)
#RESULTAT Résultat :

Si "USD" est sélectionné, CurrencySelected = "USD".

Si aucune devise n'est sélectionnée, CurrencySelected = BLANK().
Si une devise est sélectionnée (ISFILTERED retourne TRUE) :

La mesure construit un texte dynamique en combinant :

La chaîne "Sales Amount in "
La valeur de la devise sélectionnée (CurrencySelected)
La chaîne " vs All Currencies"
Exemple de résultat :
"Sales Amount in USD vs All Currencies".
Si aucune devise n'est sélectionnée (ISFILTERED retourne FALSE) :

La mesure retourne le message statique :

"Please, Select Currency from the Dropdown Menu".
****SELECTEDVALUE*** est une fonction en DAX qui retourne la valeur visible unique
d'une colonne dans le contexte actuel de calcul. Elle est particulièrement utile
lorsque vous travaillez avec des filtres ou des segments (slicers) dans Power BI.
SalesAmountSelectedCurrency =

var SalesAmount = SUM(fact_InternetSales[SalesAmount])

var USDollars = CALCULATE(SalesAmount,dim_Currency[CurrencyAlternateKey]="USD")

var CADDollars = CALCULATE(SalesAmount,dim_Currency[CurrencyAlternateKey]="CAD")

var GBP = CALCULATE(SalesAmount,dim_Currency[CurrencyAlternateKey]="GBP")

var AUD = CALCULATE(SalesAmount,dim_Currency[CurrencyAlternateKey]="AUD")

var DEM = CALCULATE(SalesAmount,dim_Currency[CurrencyAlternateKey]="DEM")

var FRF = CALCULATE(SalesAmount, dim_Currency[CurrencyAlternateKey]="FRF")

RETURN

IF(SELECTEDVALUE(dim_Currency[CurrencyAlternateKey])="USD", USDollars,
IF(SELECTEDVALUE(dim_Currency[CurrencyAlternateKey])="CAD", CADDollars,
IF(SELECTEDVALUE(dim_Currency[CurrencyAlternateKey])="GBP", GBP,
IF(SELECTEDVALUE(dim_Currency[CurrencyAlternateKey])="AUD", AUD,
IF(SELECTEDVALUE(dim_Currency[CurrencyAlternateKey])="DEM", DEM,
IF(SELECTEDVALUE(dim_Currency[CurrencyAlternateKey])="FRF", FRF,
"No Currency Selected"))))))
#partie cours//
créer un visuel histogramme empilé qui decrit le CA "revene" par pays "country"
selon lesfabricants"manufactuer" donc laxe x est contry et laxeyest somme e
revue=CA et legene estmanuffactuer
si le visuel est a peine visible on va filtrer et afficher N premiers etdonner le
nbr et on peut desactiver le filtre
autre technique est de grouper les donnees de la colonne "manufacturer"
f drill down k nbda aamla b date visuel
donc on a un flech dayer louta edhka yaaml mode dexploration yaani annnee wahda
ykhalina nchoufou les mois et tt
flech double edhka ykhalina nchoufou les diffrents hiaearchie pr toutes les annness
par defaut la surbrillance est activé
constat: le drill down se materialise par lajout de ces boutons visuels
fazet pourcentage tetaaml aala jnab
quetion:est ce que le donnes dun ficher source quivont etre iportes et stockes
localement dans power bi von etr modfies automatiquuemen suie a une ise a ju de ce
fichier orce ?
repose: non es donnes iportes et stcke localement danspowrb ne sot as auomatiqueen
mises a jour lorsque le fichier source change
constat:$il faut actualiser manuellement les donnes