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.
I have a table similar to the following:
My aim is to have cards that show the number of assessments where the "Days" column is <= 5, with a slicer on the level (Levels 1/2/3). I have a separate Level table that I have built relationships to each of the Maths/English/Science Level columns which I slice on:
The model looks like the following:
Where the active relationship is Level -> English Level.
My DAX formulas are like:
Maths = CALCULATE(COUNT('Data'[Maths Level]), FILTER('Data', 'Data'[Maths Days] <= 5), USERELATIONSHIP('Data'[Maths Level],'Slicer'[Level])) + 0
and similar for the English and Science. However, only the English measure has the expected values when applying the slicer "Level". Am I doing something wrong? I thought that USERELATIONSHIP would make the chosen inactive relationship become active?
Sample file: https://1drv.ms/u/s!AsgvTV9XMzI1hYRb7VJx2OFLw6_z4g
Thank you for reading!
Solved! Go to Solution.
Hi @oliverblane
Try these measures:
English =
CALCULATE(
COUNT( 'Data'[English Level] ),
FILTER( Data, 'Data'[English Days] <= 5 ),
USERELATIONSHIP ( 'Data'[English Level], 'Slicer'[Level] )
) + 0
Maths =
CALCULATE(
COUNT( 'Data'[Maths Level] ),
'Data'[Maths Days] <= 5, REMOVEFILTERS(Data[English Level]),
USERELATIONSHIP ( 'Data'[Maths Level], 'Slicer'[Level] )
) + 0
Science =
CALCULATE(
COUNT( 'Data'[Science Level] ),
'Data'[Science Days] <= 5 ,REMOVEFILTERS(Data[English Level]),
USERELATIONSHIP ( 'Data'[Science Level], 'Slicer'[Level] )
)
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Hi @oliverblane
Try these measures:
English =
CALCULATE(
COUNT( 'Data'[English Level] ),
FILTER( Data, 'Data'[English Days] <= 5 ),
USERELATIONSHIP ( 'Data'[English Level], 'Slicer'[Level] )
) + 0
Maths =
CALCULATE(
COUNT( 'Data'[Maths Level] ),
'Data'[Maths Days] <= 5, REMOVEFILTERS(Data[English Level]),
USERELATIONSHIP ( 'Data'[Maths Level], 'Slicer'[Level] )
) + 0
Science =
CALCULATE(
COUNT( 'Data'[Science Level] ),
'Data'[Science Days] <= 5 ,REMOVEFILTERS(Data[English Level]),
USERELATIONSHIP ( 'Data'[Science Level], 'Slicer'[Level] )
)
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Transform your dataset in "a DAX way", then all things get much easier,
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thank you for your reply @CNENFRNL, I agree that having the data in that format makes things a lot easier. How would I transform my original dataset to that format using DAX?
I doubt it; even if possible, the complexity is beyond imagination. Above all, DAX isn't designed for ETL process in nature; all transformation of dataset is supposed to be conducted in PQ.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi @oliverblane
What is that "'Learner Info Daily" table??
Try this:
Maths =
CALCULATE(
COUNT( 'Data'[Maths Level] ),
FILTER( ALLEXCEPT('Data','Data'[Maths Level]), 'Data' <= 5 ),
USERELATIONSHIP ( 'Data'[Maths Level], 'Slicer'[Level] )
) + 0
if it does not work:
Can you post sample data as text and expected output? or share a PBIX [Power BI file] after removing sensetive data?
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Hi @VahidDM, thank you for your reply.
https://1drv.ms/u/s!AsgvTV9XMzI1hYRb7VJx2OFLw6_z4g
I have attached a PBIX file with sample data. When no slicer is selected, the output of the 3 cards should be Maths=5, English=6, Science=7, but as you will see, only English is correct. This is the same when the slicers are selected, for example when Level 1 is selected, the cards should show Maths=1, English=3, Science=2.
(Also, the learner info daily table was me forgetting to change a table name when writing the question - sorry!).
Thanks for your help.
Hi @VahidDM.
Yes, I tried that code but unfortunately all it did was stop the measure from changing with the slicer.
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |