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 ALL,
Kindly request you all to help me with this scenario -
Hi All,
So the requirement is as follows -
Dim table value
Column 1
A
B
C
Fact Table
Sales Column2
10 A+B
20 A
30 C
When user selects A then both row 1 and 2 should be displayed . Also when the user selects A & B both 1 and 2 rows should be displayed.
Now i have written a DAX which fulfills my 1st requirement i.e. working fine in single selection, but fails in multiple selection.
Dax is as follows -
Sales = VAR Column1= SELECTEDVALUE ( DimTable[Column1]) VAR Column2 = SELECTEDVALUE ( Salestable[Column2]) RETURN IF ( find( Column1,Column2 , 1, 0 ) > 0, SUM (salestable[Sales]), BLANK() )
I am thinking that you likely need to use VALUES to get both values in the event of a multi-selection scenario. After that perhaps something like a CROSSJOIN? Perhaps do a COUNTROWS of the CROSSJOIN and if not blank return a 1, filter the visual with that measure returning a 1?
Hello @Greg_Deckler
kindly request you to provide extended help if possible on the changes you suggested. However i am not getting it right as of now:(
Regards
Vishy
Can I suggest creating a manual table in the module with the values below then you create a many to many relationship to the fact table.
You link the key to the fact table, and use the second column in your slicer.
Key Column For Slicer
A A
A+B A
A+B B
B B
C C
Let me know if this works.
This is exactly what I need, BUT I also need to filter out the "Duplicate" ID values to only show a consolidated view;
I also need to remove the "Column for slicer" column in the end to show ID and key ONLY. Any ideas community?
ID Key Column For Slicer
1 A A
2 A+B A
2 A+B B
3 B B
4 C C
Hi Belisqui,
I had though about this approach but in production there are multiple values of sim type and in that case this approach doesnt seem to be ethically correct.
however i have reached at a stage wherein user selects any combination of the sim type and if it exists in the fact table it gives the result. but it doesnt search as individual string. the updated logic is as follows -
I tried by creating two additional DAX fields and giving inactive relations on them. There are two issues with this approach.
1. I am getting a blank value. Not able to remove that
2. It is only able to solve if you have one + symbol
First = LEFT(Sales[Column1],IFERROR( SEARCH("+",Sales[Column1],1)-1,99)) Second = right(Sales[Column1],IFERROR( SEARCH("+",Sales[Column1],1)-1,0)) Sales 2 = VAR Column1= SELECTEDVALUE ( dim[Column1]) VAR Column2 = SELECTEDVALUE ( Sales[Second]) RETURN CALCULATE( SUM (Sales[Sales]), find( Column1,Sales[First] , 1, 0 ) > 0 ,USERELATIONSHIP(dim[Column1],Sales[First]) )+CALCULATE( SUM (Sales[Sales]), find( Column1,Sales[Second] , 1, 0 ) > 0 ,USERELATIONSHIP(dim[Column1],Sales[Second]) )-CALCULATE( SUM (Sales[Sales]), find( "+",Sales[Column1] , 1, 0 ) > 0 ,USERELATIONSHIP(dim[Column1],Sales[Second]) )
Hi @amitchandak -
I will try the method suggested by you however i am able to implement a way by spilting the column and searching in each column and checking wether it is > 0 and having this measure at a visual level filter. But only issue is spilting the column because if there is a combination of A+B+C then it doesnt create a dynamic 3rd measure for the search -
measures created are as below -
Hi Amit,
The query which you have used for 1st and 2nd is perfect similar to the spilt that we can achieve in Power Query.
However in the next steps rather than using in active relationship and getting blank we can use find and ineither query to get the desired result.
Now my only concern is how to get dyanamically the spilt without creating a new column i.e. is there any function in DAX which can search a word in a sentence and filter values accordingly.
I want to thanks all of you for the efforts in helping me out.
One of the solution is to split the column and then try the formula
Hi ,
I am using table visualization and the user wants to see the same number of rows i.e. A+B as a value in the column , if i spilt and follow the steps i would get additional rows for B as well. In that case how the sales amount would get spilted because we dont have sales amount at individual column 1 gran. I am attaching screen shot for reference as well for what user is expecting
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |