Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |