Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I’m stuck with my Power Bi
I have a history table with an id, date of historisation, state. Datas are logged every month.
My needs :
I would like to compare the actual data to another month selected by the user by a filter
Needed Indicators:
Nb of Pv with Etat = Exploitable in the actual month
Gain indicator for Nb of Pv with Etat = Exploitable vs selected month
Nb of Gain* (actual month) vs selected month
Nb of Lost* (actual month) vs selected month
*Gain formula :
If (Actual Etat=Exploitable and (Pev Etat = Inexploitable or Pev Etat=Non contractualisé)) Then flag = 1
*Lost formula :
If ( (Actual Etat= Inexploitable or Actual Etat=Non contractualisé) and Pev Etat = Exploitable) Then flag = 1
2 flags Gain, Lost to a tab
I already get results but i don’t know if i ‘m in the good way.
Some of my mesures:
Date Max = CALCULATE(
MAX( DateHisto[Date_Histo] ),
ALL(DateHisto)
)
PV exploitable = CALCULATE(DISTINCTCOUNT(Patrimoine[Fk_Pv]),FILTER(ALL (DateHisto[Date_Histo]),DateHisto[Date_Histo]=[Date Max]),FILTER(ALL (Etat),Etat[StatutId]=1))
PV exploitable Prev = CALCULATE(DISTINCTCOUNT(Patrimoine[Fk_Pv]),FILTER(ALL (Etat),Etat[StatutId]=1))
Actual Etat = CALCULATE(SELECTEDVALUE(Patrimoine[Statut]),FILTER(ALL (DateHisto[Date_Histo]),DateHisto[Date_Histo]=[Date Max]))
EtatId a date = CALCULATE(SELECTEDVALUE(Patrimoine[StatutId]),FILTER(ALL (DateHisto[Date_Histo]),DateHisto[Date_Histo]=[Date Max]))
Prev Etat = CALCULATE(SELECTEDVALUE(Patrimoine[Statut]))
EtatId Prec = CALCULATE(SELECTEDVALUE(Patrimoine[StatutId]))
Gain = IF(isblank(_Mesures[EtatId a date]),BLANK(),if(_Mesures[EtatId a date]=1 && _Mesures[EtatId Prec]<=0,1,0))
Lost = IF(isblank(_Mesures[EtatId a date]),BLANK(),if(_Mesures[EtatId a date]<=0 && _Mesures[EtatId Prec]=1,1,0))
For the moment i'm stuck to count the number of Gain* or Lost*.
Is it the good way to create my mesures ?
Thanks for your help.
Data model
Solved! Go to Solution.
Hi @Madxfr ,
I download your sample file and I find you build a single select slicer by "Feuil1"[Date_Histo]. This will cause your problem. If you select 1/20/2022 in your slicer. It will filter "Feuil1" table to show only Fk_Pv which contain date 1/20/2022. Fk_Pv= 2000 doesn't contain date 1/20/2022, so you couldn't find 2000 in your table visual.
Here I suggest you to use unrelated calendar to build the slicer and create a measure to calculate Gain.
Prev Etat =
VAR _SELECT = SELECTEDVALUE('Calendar'[Date])
RETURN
CALCULATE(MAX(Feuil1[Statut]),FILTER(Feuil1,Feuil1[Date_Histo] = _SELECT))
Last Etat = CALCULATE(SELECTEDVALUE(Feuil1[Statut]),FILTER(ALL (Feuil1[Date_Histo]),Feuil1[Date_Histo]=[Date Max]))
M_Gain =
VAR _T =
ADDCOLUMNS (
SUMMARIZE (
Feuil1,
Feuil1[Fk_Pv],
Feuil1[IsNac],
Feuil1[NAT],
"Prev", [Prev Etat],
"Last Etat", [Last Etat]
),
"Gain",
IF ( [Prev] <> [Last Etat] && [Last Etat] = "Exploitable", 1, 0 )
)
RETURN
SUMX ( _T, [Gain] )
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Most of this looks ok. Can you please explain what you are trying to achieve with this?
Prev Etat = CALCULATE(SELECTEDVALUE(Patrimoine[Statut]))
Usually SELECTEDVALUE is used outside of CALCULATE
Hello,
You are right, i change for
Prev Etat = SELECTEDVALUE(Patrimoine[Statut])
EtatId Prec = SELECTEDVALUE(Patrimoine[StatutId])
Results are the same.
My problem is to get the sum of In (Gain) or sum of Lost.
In (Gain) is a mesure depending of actual EtatId and previous Etatid of the row.
The DAX is :
As you can see on the table it's ok, but the total is wrong and if i add a card with the mesure i always get 1
I don't know what's wrong.
Always getting 1 as a result points to a row context issue. You may want to read about iterator functions like SUMX.
I tried:
That's not what I meant - sorry for being unclear. Your Total value for your [ln] measure needs to take into account that you have multiple rows in you filter context. That's where you need to do the SUMX, not in a new measure.
Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with.
"If (Actual Etat=Exploitable and (Pev Etat = Inexploitable or Pev Etat=Non contractualisé)) Then flag = 1"
Based on what? What is the identifier that ties the rows together between dates? Do you have a primary key of sorts? Is it the combination of Fk_Pv,Fk_Dp and Fk_ModeleCom ?
See attached for my version so far.
You cannot add new rows in Power BI. You can only take rows away (filter them). That new line will have to be in its own visual.
Hi @Madxfr ,
I download your sample file and I find you build a single select slicer by "Feuil1"[Date_Histo]. This will cause your problem. If you select 1/20/2022 in your slicer. It will filter "Feuil1" table to show only Fk_Pv which contain date 1/20/2022. Fk_Pv= 2000 doesn't contain date 1/20/2022, so you couldn't find 2000 in your table visual.
Here I suggest you to use unrelated calendar to build the slicer and create a measure to calculate Gain.
Prev Etat =
VAR _SELECT = SELECTEDVALUE('Calendar'[Date])
RETURN
CALCULATE(MAX(Feuil1[Statut]),FILTER(Feuil1,Feuil1[Date_Histo] = _SELECT))
Last Etat = CALCULATE(SELECTEDVALUE(Feuil1[Statut]),FILTER(ALL (Feuil1[Date_Histo]),Feuil1[Date_Histo]=[Date Max]))
M_Gain =
VAR _T =
ADDCOLUMNS (
SUMMARIZE (
Feuil1,
Feuil1[Fk_Pv],
Feuil1[IsNac],
Feuil1[NAT],
"Prev", [Prev Etat],
"Last Etat", [Last Etat]
),
"Gain",
IF ( [Prev] <> [Last Etat] && [Last Etat] = "Exploitable", 1, 0 )
)
RETURN
SUMX ( _T, [Gain] )
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I found the solution.
I re-think the mesure, i add a new column with this formula :
User | Count |
---|---|
107 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |