Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 -
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |