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.
Dear PowerBI Community,
I'm looking for a dax-measure that counts the rows of th comissions-table which contain multiple codes that I have selected with a slicer.
The result in this case would be "2"
So far I have found the containsstrings command, but I don't know how to combine the selected codes
Do you have any advise?
Solved! Go to Solution.
@stu3000 , Try a approch like this
Power BI- Text Part slicer to filter/search text - https://youtu.be/MKKWeOqFG4c
Dear @amitchandak
thanks for your reply. It's a good approach, however I couldn't really solve the problem with it yet. Let me try to explain it better with the following example:
I have
So far I have the following measure which works fine when only one code is selected:
AffectedCommissions =
var selectedcode = SELECTEDVALUE(CodesTable[Codes])
RETURN
calculate(COUNT(CommissionsTable[CommissionNo]),CONTAINSSTRING(CommissionsTable[Codes],selectedcode))
However, if more than one code is selected with the slicer, only the rows which contain the selected codes should be counted. For example, if Codes 3, 4 and 5 are selected, then the result must be 2 (CommissionNo 3 and 6).
Furthermore, the Codes in the orders are not alway in the same order.
Is it possible to adjust the measure accordingly?
Hi @stu3000 ,
Please try below steps:
1. below is my test table
Table1:
Table2:
2. create a measure with below dax formula
Measure =
VAR _a =
ADDCOLUMNS (
'Table2',
"Flag",
IF (
SUMX (
ADDCOLUMNS (
SELECTCOLUMNS ( 'Table1', "Selected", [Slicer] ),
"Max Codes", IF ( CONTAINSSTRING ( [Codes], [Selected] ), 1, 0 )
),
[Max Codes]
)
= COUNTROWS ( SELECTCOLUMNS ( 'Table1', "Selected", [Slicer] ) ),
1,
0
)
)
RETURN
SUMX ( _a, [Flag] )
3. add a slicer with field, add a cred visual with measure
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dear @amitchandak
thanks for your reply. It's a good approach, however I couldn't really solve the problem with it yet. Let me try to explain it better with the following example:
I have
So far I have the following measure which works fine when only one code is selected:
AffectedCommissions =
var selectedcode = SELECTEDVALUE(CodesTable[Codes])
RETURN
calculate(COUNT(CommissionsTable[CommissionNo]),CONTAINSSTRING(CommissionsTable[Codes],selectedcode))
However, if more than one code is selected with the slicer, only the rows which contain the selected codes should be counted. For example, if Codes 3, 4 and 5 are selected, then the result must be 2 (CommissionNo 3 and 6).
Furthermore, the Codes in the orders are not alway in the same order.
Is it possible to adjust the measure accordingly?
Hi @stu3000 ,
Please try below steps:
1. below is my test table
Table1:
Table2:
2. create a measure with below dax formula
Measure =
VAR _a =
ADDCOLUMNS (
'Table2',
"Flag",
IF (
SUMX (
ADDCOLUMNS (
SELECTCOLUMNS ( 'Table1', "Selected", [Slicer] ),
"Max Codes", IF ( CONTAINSSTRING ( [Codes], [Selected] ), 1, 0 )
),
[Max Codes]
)
= COUNTROWS ( SELECTCOLUMNS ( 'Table1', "Selected", [Slicer] ) ),
1,
0
)
)
RETURN
SUMX ( _a, [Flag] )
3. add a slicer with field, add a cred visual with measure
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
this works perfectly. Thanks, Binbin Yu!!
@stu3000 , Try a approch like this
Power BI- Text Part slicer to filter/search text - https://youtu.be/MKKWeOqFG4c
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 |
---|---|
39 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |