Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Community,
I've one question, maybe a silly one, but I already wasted some time trying to solve the following requirement...
I've the following very simple star schema model in my PBI file, and what I want to do is to roll up the value measure per Country Name... I'm able to roll up the value by date, but I need to group the RollupValue by Country Name.
Probably I'm missing something very simple....
Have you ever have to solve a problem like this one? if so, can you share your solution with me?
Many Thanks, Lacq
Solved! Go to Solution.
Hi @lacq
First add a calculated column in your FactValues
Use this Column in the Visual
CountryName = related(DimCountry[CountryName])
Now you can use this MEASURE to get the Cumulative
Cumulative = CALCULATE ( SUM ( FactValues[Value] ), FILTER ( ALLEXCEPT ( FactValues, FactValues[CountryName] ), FactValues[Date] <= SELECTEDVALUE ( FactValues[Date] ) ) )
Hi @lacq
First add a calculated column in your FactValues
Use this Column in the Visual
CountryName = related(DimCountry[CountryName])
Now you can use this MEASURE to get the Cumulative
Cumulative = CALCULATE ( SUM ( FactValues[Value] ), FILTER ( ALLEXCEPT ( FactValues, FactValues[CountryName] ), FactValues[Date] <= SELECTEDVALUE ( FactValues[Date] ) ) )
Excellent @Zubair_Muhammad, it works like a charm..... Well done!!!!
Now imagine that I've another group by, e.g. City_Name... how can I change the DAX to manage it, i.e.
Thanks in advance, lacq
I think you just need to replace CountryName with CityName in DAX.
Hi @Zubair_Muhammad,
I'm sorry I didn't explain myself well.... when I said that I have a new "group by", City_name, I would like to maintain the Country Name Group... maybe the City_Name was a bad example... let's try with ProductName, so the same product might be sold in different countries.. and I would like to roll up the value by Country_Name and Product_Name .
Thanks again, lacq
In that case, I believe you will just need to extend the
ALLEXCEPT ( FactValues, FactValues[CountryName] )
to
ALLEXCEPT ( FactValues, FactValues[CountryName], FactValues[ProductName] )
Hello @lacq
Just poking in with slightly different approach which does not require bringing in the country name string into the fact table via RELATED()
= CALCULATE ( SUM ( FactValues[Value] ), FILTER ( ALL ( FactValues ), FactValues[Date] <= MAX ( FactValues[Date] ) && FactValues[CountryKey] IN VALUES ( DimCountry[CountryKey] ) ) )
@Zubair_Muhammad Really liked your SELECTEDVALUE() over the dates
Thank, Nick -
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |