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, I have a what I think should be a relatively simple problem which I need to sort in DAX.
Attr A | Input Value | Required Measure |
Attr A1 | 1 | 1 |
Attr A2 | 2 | 1 |
Attr A3 | 3 | 1 |
Attr A4 | 1 | |
Attr A5 | 1 | |
Attr A6 | 6 | 6 |
The table above describe the output measure which should be the Input value @ Attr A1 when Attr A = anything except A6. When Attr A = Attr A6 the value should be 6
Solved! Go to Solution.
Hi @tonie_tollig,
According to your description, I think you want to replace result based on current row number, right? (if current row is last row, output last row 'input value' otherwise out first row 'input value')
If this is a case, you can add index column in query edit and use following measure to achieve your requirement:
Measure = VAR _current = MAX ( Table1[Index] ) VAR _min = MINX ( ALLSELECTED ( Table1[Index] ), [Index] ) VAR _max = MAXX ( ALLSELECTED ( Table1[Index] ), [Index] ) RETURN IF ( _current = _max, CALCULATE ( MAX ( Table1[Input Value] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Index] = _max ) ), CALCULATE ( MIN ( Table1[Input Value] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Index] = _min ) ) )
Regards,
Xiaoxin Sheng
Hi,
Write this calculated column formula
=IF([Attr A]="Attr A6",6,1)
Hope this helps.
Hi, thank you for the feedback. Unfortunately I wasn't very clear. I have used the values 1-6 purely as placeholders. The measure should the input value @ Attr A1 where Attr A = Attr A1, Attr A2...Attr A5 but should be the input value @ Attr A6 when Attr A = Attr A6.
Hope it makes sense.
I am still not clear. Someone else who understand your question will help.
Attr A | Input Value | Required Measure |
Attr A1 | Value 1 | Value 1 |
Attr A2 | Value 2 | Value 1 |
Attr A3 | Value 3 | Value 1 |
Attr A4 | Value 1 | |
Attr A5 | Value 1 | |
Attr A6 | Value 4 | Value 4 |
Hi @tonie_tollig,
According to your description, I think you want to replace result based on current row number, right? (if current row is last row, output last row 'input value' otherwise out first row 'input value')
If this is a case, you can add index column in query edit and use following measure to achieve your requirement:
Measure = VAR _current = MAX ( Table1[Index] ) VAR _min = MINX ( ALLSELECTED ( Table1[Index] ), [Index] ) VAR _max = MAXX ( ALLSELECTED ( Table1[Index] ), [Index] ) RETURN IF ( _current = _max, CALCULATE ( MAX ( Table1[Input Value] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Index] = _max ) ), CALCULATE ( MIN ( Table1[Input Value] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Index] = _min ) ) )
Regards,
Xiaoxin Sheng
Thanks, very intersting way in solving the problem
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |