Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All
Referring to my first table, my DAX formula for ALL is: Calculate(Sum(Players), ALL(Table[Sport]))
Can I write a generic ALL statement so that if the user changes the context (instead of sport, use location), the % of Total will still work?
I have tried to reference the entire table within the ALL but it doesn't seem to work. Thanks!
Sport | Players | ALL DAX | % of Total |
Tennis | 25 | 60 | 42% |
Basketball | 20 | 60 | 33% |
Football | 15 | 60 | 25% |
60 | |||
Location | Players | ALL DAX | % of Total |
ABC | 12 | 60 | 20% |
DFC | 25 | 60 | 42% |
EFG | 23 | 60 | 38% |
60 |
If location is a seperate table, you'll need a different query than if it's in the same table.
What does your table structure look like at the moment?
@swwong1 , please send a screenshot of the relationship view. It will really help us understand the best way to solve this.
It sounds like the SUM(Fact_Table[Players]) is your main measure, coming from what I'm calling the Fact_Table.
I'm going to assume that TableName[Sports] is a dimension table that has a relationship to Fact_Table?
Does [Location] column reside in another dimension table with a different relationship to Fact_Table?
If so, you could write the following DAX:
All Calc := CALCULATE( SUM(Fact_Table[Players]) ,ALL(Fact_Table) )
This will remove any filter on any column in the (expanded) Fact_Table. This means that any filter on any column that is related to Fact_Table will be removed as well.
Again, knowing what your data model looks like (and which columns belong to which tables) will greatly help us solve this problem.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |