Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have working on something for long and have related queries (https://community.powerbi.com/t5/Desktop/Conditional-tables-using-DAX-or-other-methods/m-p/1542696#M...) and (https://community.powerbi.com/t5/Desktop/Cross-filter-to-exclude/m-p/1452395#M608419) - while some tips have helped however i haven't managed to get to the final outcome. I suspect its my lack of explaining of expected outcome. hence providing below a detail of all the measures that i need as i have established ( dynamic tables / temp tables are not possible. It is also not possible to use what if parameter in calculation of a new column ). Hence below is details of data and expected measures :
Table 1 ( Each row represents unique Case ID and Life ID ) - this is the main table
Case ID | Life ID | Age | BMI | RandFlag | Dec | Length |
1 | A | 45 | 23 | 0 | A | 0.83 |
1 | B | 54 | 32 | 0 | A | 0.78 |
2 | C | 32 | 29 | 1 | A | 0.9 |
3 | D | 32 | 23 | 0 | CA | 0.35 |
3 | E | 31 | 27 | 0 | CA | 0.89 |
3 | F | 15 | 22 | 1 | CA | 0.99 |
4 | G | 45 | 30 | 0 | A | 0.95 |
5 | H | 24 | 28 | 0 | A | 0.91 |
6 | I | 53 | 25 | 0 | CA | 0.12 |
6 | J | 51 | 26 | 0 | CA | 0.97 |
7 | K | 32 | 25 | 1 | A | 0.34 |
7 | L | 35 | 24 | 0 | A | 0.35 |
8 | M | 34 | 29 | 0 | A | 0.99 |
9 | N | 31 | 25 | 0 | D | 0.23 |
Table 2 - condition table . This also represents case ID and life ID per row however one case and Life ID can repeat if they have multiple conditions. the case IDs and Life IDs are a subset of above and do not have to inlcude all.
Case ID | Life ID | Condition |
3 | E | X1 |
6 | I | X2 |
1 | B | X3 |
3 | D | X2 |
What if Parameter
Threshold = From (0,1)
FILTERS
Filters are applied based on Age , BMI and Conditions(table2)
lets take the below filters for our example
Age | BMI | Condition | |
Filters | 30-50 | 18-31 | X1 |
while Age and BMI work by filtering out the cases which don't fall in that range - X1 is suppose to work the excluding the case associated with X1 i.e. Case ID 3. More explained below.
threshold = 0.90 for this example
Expected Measures / calculations
Note that below calculations are in an order like a flow chart. each stage is a subset of other
1) Count Case ID (Distinct) : this is simplest. Distinct count of Case ID from table 1 = 9
2a) Count_CaseID_filtered (Distinct) : Count of case IDs which have been filtered out (due to 1 or more reason ) =4 , [Case ID - 1,3,5,6 as 1B is kicked out due to Age & even BMI , 3E is kicked out due to BMI and condition , 3F is kicked out due to Age, 5H and 6J are kicked out due to age]. While case and Life are filtered out - we count the whole case as filtered out, hence distinct count is 4
2b) Count_CaseID_not_filtered (Distinct) = 5 = whatever Case ID was not filtered out is left to count here i.e. 2,4,7,8,9. note that while say 1A may not be filtered out but since 1b was filtered out - whole case 1 gets filtered out and is not expected to be in the counts.
2a + 2b should always = 1
2a.1) % A Rate_filtered = of the cases filtered out [1,3,5,6] how many have Dec = A given as % of that group total : Answer = 2 [case Ids1,5]/4 = 0.5
2b.1) Count_CaseID_RandFlag (Distinct) : of the non filtered cases [2,4,7,8,9] how many have RandFlag = 1 : Answer = 2 , Cases 2,7
2b.1.1) % A Rate_rand_flag = of the cases [2,7] how many have Dec = A given as % of that group total : Answer = 2[both 2,7 are A]/2 = 1
2b.2) Count_CaseID_NonRandFlag (Distinct) : Count of the cases which are non filtered and non Rand flag i.e. 4,8,9 = 3
2b.2.1) % A Rate_nonrand_flag = of the cases [4,8,9] how many have Length>= Threshold(what if parameter set at 0.9) given as % of that group total : Answer = 2[both 4,8 are >=0.9]/3 = 0.66
2b.1 + 2b.2 should always = 2b
3) Overall Rate % = coount of cases which were A in Filtered () + count of cases which were A in RandFlag + count of cases which were >= threshold in NonRandFlag / total cases : Answer = 2+2+2/9 = 6/9 = 0.66
Solved! Go to Solution.
Hi @pbhat89
I build a sample like yours to have a test.
Main Table:
Condition Table:
I build an Age table ,a BMI table and parameter for slicer.
Age = GENERATESERIES(MIN('Main table'[Age]),MAX('Main table'[Age]),1)
BMI = GENERATESERIES(MIN('Main table'[BMI]),MAX('Main table'[BMI]),1)
Parameter = GENERATESERIES(0, 1, 0.1)
Due to there are several measures, I will show you some of them.
2a) Count_CaseID_filtered (Distinct) =
VAR _CaseIDCondition =
SUMMARIZE (
FILTER (
ALL ( 'Conditon' ),
'Conditon'[Condition] = SELECTEDVALUE ( 'Conditon'[Condition] )
),
'Conditon'[Case ID]
)
VAR _Age =
GENERATESERIES ( MIN ( Age[Age] ), MAX ( Age[Age] ) )
VAR _BMI =
GENERATESERIES ( MIN ( BMI[BMI] ), MAX ( BMI[BMI] ) )
VAR _CaseIDMain =
SUMMARIZE (
FILTER (
ALL ( 'Main table' ),
NOT ( 'Main table'[Age] IN _Age )
|| NOT ( 'Main table'[BMI] IN _BMI )
|| 'Main table'[Case ID] IN _CaseIDCondition
),
'Main table'[Case ID]
)
RETURN
COUNTAX ( _CaseIDMain, [Case ID] )
2a.1) % A Rate_filtered =
VAR _CaseIDCondition =
SUMMARIZE (
FILTER (
ALL ( 'Conditon' ),
'Conditon'[Condition] = SELECTEDVALUE ( 'Conditon'[Condition] )
),
'Conditon'[Case ID]
)
VAR _Age =
GENERATESERIES ( MIN ( Age[Age] ), MAX ( Age[Age] ) )
VAR _BMI =
GENERATESERIES ( MIN ( BMI[BMI] ), MAX ( BMI[BMI] ) )
VAR _CaseIDMain =
SUMMARIZE (
FILTER (
ALL ( 'Main table' ),
AND (
NOT ( 'Main table'[Age] IN _Age )
|| NOT ( 'Main table'[BMI] IN _BMI )
|| 'Main table'[Case ID] IN _CaseIDCondition,
'Main table'[Dec] = "A"
)
),
'Main table'[Case ID]
)
VAR _CountIDDecA =
COUNTX ( _CaseIDMain, [Case ID] )
RETURN
DIVIDE ( _CountIDDecA, [2a) Count_CaseID_filtered (Distinct)] )
2b.1) Count_CaseID_RandFlag (Distinct) =
VAR _CaseIDCondition =
SUMMARIZE (
FILTER (
ALL ( 'Conditon' ),
'Conditon'[Condition] = SELECTEDVALUE ( 'Conditon'[Condition] )
),
'Conditon'[Case ID]
)
VAR _Age =
GENERATESERIES ( MIN ( Age[Age] ), MAX ( Age[Age] ) )
VAR _BMI =
GENERATESERIES ( MIN ( BMI[BMI] ), MAX ( BMI[BMI] ) )
VAR _CaseIDMain =
SUMMARIZE (
FILTER (
ALL ( 'Main table' ),
NOT ( 'Main table'[Age] IN _Age )
|| NOT ( 'Main table'[BMI] IN _BMI )
|| 'Main table'[Case ID] IN _CaseIDCondition
),
'Main table'[Case ID]
)
VAR _Casenotfilter =
SUMMARIZE (
FILTER (
'Main table',
NOT ( 'Main table'[Case ID] )
IN _CaseIDMain
&& 'Main table'[RandFlag] = 1
),
'Main table'[Case ID]
)
RETURN
COUNTAX ( _Casenotfilter, [Case ID] )
And so on.
Result is as below.
You can download the pbix file from this link: DAX hierarchical complex calculations
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.
Hi @pbhat89
You can try to build a "Feature" Table as a new slicer table.
And then use it to update your measures.
If you want to select single Feature in slicer you can use selectedvalue in dax , if you want to select multiple values in slicer you can try values in dax.
Then add a filter in measure.
like
VAR _FeatureID =
SUMMARIZE (
FILTER (
ALL ( 'Main table' ),
'Main table'[Feature] in VALUES(Feature[Feature])
),
'Main table'[Case ID]
)
Then use this filter in below Maintable filters to calculate the count.
like
Var _Result =
countx(Filter(All('Main Table'),filter1&&filter2&&...&&Max('Main table'[Case ID])in _FeatureID))
...
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.
Hi @pbhat89
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
Hi @pbhat89
Your filters in example may be Age from 30 to 50, BMI from 18 to 31, conditon = X1 , threshold > 0.90 for this example.
Here I have some questions:
1. Did you build relationships by Case ID columns in two tables?
2. How did you filter your tables, by slicers or filter fields?
If I relate two tables and I use this filter as above. It will only show blank,
So if I use your filter, your measure seems that won't show results you want.
And the smallest number in your sample BMI is 22, how could you get 18 to 31?
If I have any misunderstandings, please show me more details or samples.
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.
Hi Rico,
1. Did you build relationships by Case ID columns in two tables? -- No i didn't. To get some of my measures i used Case ID 'not' in' and isfiltered commands to get my results. I understand it may not be the best way. The idea is for this to work as an exclude (not include filter) hence i only got to this approach to make it work.
2. How did you filter your tables, by slicers or filter fields? --- if i keep as slicers - i can't created a new column say Filter_age = T/F as What if parameters can't be used to create columns , only measures. I used filters directly.
3. Please correct BMI as 22-31. you are right, i created that mistake while creating this data sample.
Not sure what your table doesn't show. I suspect some error. As you can see from the data -- with Filters of Age , BMI and X1(case ID 3 filtered out) = there are cases left (2,4,7,8,9)
Hi @pbhat89
I build a sample like yours to have a test.
Main Table:
Condition Table:
I build an Age table ,a BMI table and parameter for slicer.
Age = GENERATESERIES(MIN('Main table'[Age]),MAX('Main table'[Age]),1)
BMI = GENERATESERIES(MIN('Main table'[BMI]),MAX('Main table'[BMI]),1)
Parameter = GENERATESERIES(0, 1, 0.1)
Due to there are several measures, I will show you some of them.
2a) Count_CaseID_filtered (Distinct) =
VAR _CaseIDCondition =
SUMMARIZE (
FILTER (
ALL ( 'Conditon' ),
'Conditon'[Condition] = SELECTEDVALUE ( 'Conditon'[Condition] )
),
'Conditon'[Case ID]
)
VAR _Age =
GENERATESERIES ( MIN ( Age[Age] ), MAX ( Age[Age] ) )
VAR _BMI =
GENERATESERIES ( MIN ( BMI[BMI] ), MAX ( BMI[BMI] ) )
VAR _CaseIDMain =
SUMMARIZE (
FILTER (
ALL ( 'Main table' ),
NOT ( 'Main table'[Age] IN _Age )
|| NOT ( 'Main table'[BMI] IN _BMI )
|| 'Main table'[Case ID] IN _CaseIDCondition
),
'Main table'[Case ID]
)
RETURN
COUNTAX ( _CaseIDMain, [Case ID] )
2a.1) % A Rate_filtered =
VAR _CaseIDCondition =
SUMMARIZE (
FILTER (
ALL ( 'Conditon' ),
'Conditon'[Condition] = SELECTEDVALUE ( 'Conditon'[Condition] )
),
'Conditon'[Case ID]
)
VAR _Age =
GENERATESERIES ( MIN ( Age[Age] ), MAX ( Age[Age] ) )
VAR _BMI =
GENERATESERIES ( MIN ( BMI[BMI] ), MAX ( BMI[BMI] ) )
VAR _CaseIDMain =
SUMMARIZE (
FILTER (
ALL ( 'Main table' ),
AND (
NOT ( 'Main table'[Age] IN _Age )
|| NOT ( 'Main table'[BMI] IN _BMI )
|| 'Main table'[Case ID] IN _CaseIDCondition,
'Main table'[Dec] = "A"
)
),
'Main table'[Case ID]
)
VAR _CountIDDecA =
COUNTX ( _CaseIDMain, [Case ID] )
RETURN
DIVIDE ( _CountIDDecA, [2a) Count_CaseID_filtered (Distinct)] )
2b.1) Count_CaseID_RandFlag (Distinct) =
VAR _CaseIDCondition =
SUMMARIZE (
FILTER (
ALL ( 'Conditon' ),
'Conditon'[Condition] = SELECTEDVALUE ( 'Conditon'[Condition] )
),
'Conditon'[Case ID]
)
VAR _Age =
GENERATESERIES ( MIN ( Age[Age] ), MAX ( Age[Age] ) )
VAR _BMI =
GENERATESERIES ( MIN ( BMI[BMI] ), MAX ( BMI[BMI] ) )
VAR _CaseIDMain =
SUMMARIZE (
FILTER (
ALL ( 'Main table' ),
NOT ( 'Main table'[Age] IN _Age )
|| NOT ( 'Main table'[BMI] IN _BMI )
|| 'Main table'[Case ID] IN _CaseIDCondition
),
'Main table'[Case ID]
)
VAR _Casenotfilter =
SUMMARIZE (
FILTER (
'Main table',
NOT ( 'Main table'[Case ID] )
IN _CaseIDMain
&& 'Main table'[RandFlag] = 1
),
'Main table'[Case ID]
)
RETURN
COUNTAX ( _Casenotfilter, [Case ID] )
And so on.
Result is as below.
You can download the pbix file from this link: DAX hierarchical complex calculations
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.
Rico - this is immensely helpful. I am now going through the structure and logic of forumales and trying to understand the flow. Just one comment - apart from Numeric fields as filters - what if i had a categorical filter in Main table :
Case ID Life ID Age BMI RandFlag Dec Length Feature
1 A 45 23 0 A 0.83 G1
1 B 54 32 0 A 0.78 G1
2 C 32 29 1 A 0.9 G2
3 D 32 23 0 CA 0.35 G1
3 E 31 27 0 CA 0.89 G2
3 F 15 22 1 CA 0.99 G1
4 G 45 30 0 A 0.95 G1
5 H 24 28 0 A 0.91 G1
6 I 53 25 0 CA 0.12 G1
6 J 51 26 0 CA 0.97 G1
7 K 32 25 1 A 0.34 G3
7 L 35 24 0 A 0.35 G1
8 M 34 29 0 A 0.99 G1
9 N 31 25 0 D 0.23 G1
and in addition to Age and BMI - this is additional filter "Feature" - i.e. if G1 is selected , rest are filtered out. so in the above 4,8,9 are left in Count_CaseID_not_filtered (Distinct) as 2 has G2 and 7K has G3
Hi @pbhat89
I think your problem is when you use slicer (Feature), your table will be filtered and your measure result is incorrect.
You can try to use all function in your measure to calculate on the all table instead of the filtered table.
You may refer to this blog: Manage "all" function
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.
Not quite sure how that addressed the Categorical feature as aspect. i am half way through implementing the above formulaes as per your recommendation. Are you saying if there is a new filter column such as "feature" which is categorical that i can't use the above? I have explored using ALL but hasn't solved the issue. what modifications to above to incorporate "Feature" also as a filter - can it be made a seperate table and then used in the same way?
Hi @pbhat89
You can try to build a "Feature" Table as a new slicer table.
And then use it to update your measures.
If you want to select single Feature in slicer you can use selectedvalue in dax , if you want to select multiple values in slicer you can try values in dax.
Then add a filter in measure.
like
VAR _FeatureID =
SUMMARIZE (
FILTER (
ALL ( 'Main table' ),
'Main table'[Feature] in VALUES(Feature[Feature])
),
'Main table'[Case ID]
)
Then use this filter in below Maintable filters to calculate the count.
like
Var _Result =
countx(Filter(All('Main Table'),filter1&&filter2&&...&&Max('Main table'[Case ID])in _FeatureID))
...
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.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |