Forum Discussion
Dax help
| Date | Value | Supplier |
| 2025-01-01 | 20 | S1 |
| 2025-01-01 | 23 | S1 |
| 2025-01-03 | 38 | S1 |
| 2025-01-03 | 52 | S1 |
| 2025-01-01 | 12 | S1 |
| 2025-01-03 | 23 | S1 |
| 2025-01-04 | 64 | S1 |
| 2025-01-01 | 14 | S2 |
| 2025-01-04 | 94 | S2 |
I have this data. And i have created a countionuse date table in power bi.
And i also have two numeric decimal parameters that goes from 0 to 2 in steps of 0.01 called S1Factor and S2Factor
My problem is that i want to create a stacked bar chart based on Date and two measures one measure that filters on Supplier = S1 and another that is based on Supplier= S2.
i want a part of my measure to do something like this.
MeasureS1 = CALCULATE(SUM('Tabel[Value] )* SELECTEDVALUE( S1Factor[S1]) , FILTER( 'Table' , 'Table'[Supplier]= "S1"))
So based on what decimal number i chose in the slicer for the parameter S1 Factor that number shall multiply the 'Table'[value].
And this works as intended
However the big problem is that i also want to make my MeasureS1 to only make the multiplication on the values that has the corresponding dates that is chosen in a dateslicer. So if i for example only chose the Date = 2025-01-01 & 2025-01-03 i only want the multiplication by the factor to happen for those two dates. But i always want all data to be visible even the date that is not chosen in the slicer.
So basically i want in the stacked barchart is that the staples for the chosen dates in the slicer 2025-01-01 & 2025-01-03 to change based on S1factor slicer. And the not chosen date in the date slicer 2025-01-04 to still have a bar in the visualisation but not be changed by the factor.
As you can see in the pictures all the bars changes when i change the factor S1 i only want the bars for 2025-01-01 & 2025-01-03 to change and 2025-01-04 to still be visable but not factored.
Thanks in advance
Hi Dronning ,
You need to create a table with dates value to use on a slicer then use the following changes to your measures:
MeasureS1 = IF( SELECTEDVALUE('Table'[Date]) IN VALUES(Dates[Date]), CALCULATE( SUM('Table'[Value]) * SELECTEDVALUE(S1Factor[S1]), FILTER( 'Table', 'Table'[Supplier] = "S1" ) ), CALCULATE( SUM('Table'[Value]), 'Table'[Supplier] = "S1" ) )Chexh file attach.
Dronning My friend MFelix has given an awesome solution, here is another version of the calculation. This will show the total correctly in the table visual if you ever decide to visualize using matrix/table visual otherwise please follow what MFelix provided. Cheers!
Measure S1 = VAR __SelectedDates = VALUES ( 'Date Slicer'[Date] ) RETURN SUMX ( FILTER ( Supplier, Supplier[Supplier] = "S1" ), VAR __S1ParameterValue = IF ( Supplier[Date] IN __SelectedDates, SELECTEDVALUE(S1Factor[S1]), 1 ) VAR __Value = Supplier[Value] * __S1ParameterValue RETURN __Value )
5 Replies
Hi Dronning ,
You need to create a table with dates value to use on a slicer then use the following changes to your measures:
MeasureS1 = IF( SELECTEDVALUE('Table'[Date]) IN VALUES(Dates[Date]), CALCULATE( SUM('Table'[Value]) * SELECTEDVALUE(S1Factor[S1]), FILTER( 'Table', 'Table'[Supplier] = "S1" ) ), CALCULATE( SUM('Table'[Value]), 'Table'[Supplier] = "S1" ) )Chexh file attach.
Dronning My friend MFelix has given an awesome solution, here is another version of the calculation. This will show the total correctly in the table visual if you ever decide to visualize using matrix/table visual otherwise please follow what MFelix provided. Cheers!
Measure S1 = VAR __SelectedDates = VALUES ( 'Date Slicer'[Date] ) RETURN SUMX ( FILTER ( Supplier, Supplier[Supplier] = "S1" ), VAR __S1ParameterValue = IF ( Supplier[Date] IN __SelectedDates, SELECTEDVALUE(S1Factor[S1]), 1 ) VAR __Value = Supplier[Value] * __S1ParameterValue RETURN __Value )Thank you very much, great solution 🙂
