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'm trying to create measures that will handle filtering on a date and time level for the start end end date.
For this I'm using a normal date slicer, and then filtering start and end time based on the min/max dates in the selected range, using two seperate tables as input variables for the start and end time.
Currently I have the measure listed below, which works as intended, but I need to figure out how to get the correct total, when not filtering on a date level.
I get that the measure returns blank when not filtering on a Date level due to the 'HASONEVALUE', but I'm using the 'HASONEVALUE' to return a single scalar value into the following if statements.
How do I feed the if statements with a single value, but get the correct total value shown at a year or month level?
Sales Revenue = VAR MinDato = CALCULATE(MIN(Periode[Dato]);ALLSELECTED(Periode[Dato])) VAR MaxDato = CALCULATE(MAX(Periode[Dato]);ALLSELECTED(Periode[Dato])) VAR MaxTime = CALCULATE(MAX('Time min filter'[Time])) VAR MAXTimeMaxDato = CALCULATE(MAX('Time max filter'[Time])) RETURN IF( HASONEVALUE(Periode[Dato]); IF(VALUES(Periode[Dato]) = MinDato; CALCULATE(Sales[Sales Revenue]; FILTER('Periode Tidspunkt';'Periode Tidspunkt'[Time] >= MaxTime)); IF(VALUES(Periode[Dato]) = MaxDato; CALCULATE(Sales[Sales Revenue]; FILTER('Periode Tidspunkt';'Periode Tidspunkt'[Time] <= MAXTidspunktMaxDato)); Sales[Sales Revenue])); BLANK())
Note:
Measure is mainly in danish so:
Dato = Date
Periode = Date table
Solved! Go to Solution.
Cracked it by more googling and turning to switch for help and referencing another table
Final measure:
Salg Oms Switch Filter = VAR MinDato = CALCULATE(MIN(Periode[Dato]);ALLSELECTED(Periode[Dato])) VAR MaxDato = CALCULATE(MAX(Periode[Dato]);ALLSELECTED(Periode[Dato])) VAR MaxTidspunkt = CALCULATE(MAX('Tidspunkt min filter'[Time])) VAR MAXTidspunktMaxDato = CALCULATE(MAX('Tidspunkt max filter'[Time])) RETURN DIVIDE( SUMX(Salg; SWITCH( TRUE(); Salg[Bogføringsdato] = MinDato;CALCULATE(Salg[Salg Omsætning]; FILTER('Periode Tidspunkt';'Periode Tidspunkt'[Time] >= MaxTidspunkt)); Salg[Bogføringsdato] = MaxDato; CALCULATE(Salg[Salg Omsætning]; FILTER('Periode Tidspunkt';'Periode Tidspunkt'[Time] <= MAXTidspunktMaxDato)); Salg[Salg Omsætning])) ;1000)
Cracked it by more googling and turning to switch for help and referencing another table
Final measure:
Salg Oms Switch Filter = VAR MinDato = CALCULATE(MIN(Periode[Dato]);ALLSELECTED(Periode[Dato])) VAR MaxDato = CALCULATE(MAX(Periode[Dato]);ALLSELECTED(Periode[Dato])) VAR MaxTidspunkt = CALCULATE(MAX('Tidspunkt min filter'[Time])) VAR MAXTidspunktMaxDato = CALCULATE(MAX('Tidspunkt max filter'[Time])) RETURN DIVIDE( SUMX(Salg; SWITCH( TRUE(); Salg[Bogføringsdato] = MinDato;CALCULATE(Salg[Salg Omsætning]; FILTER('Periode Tidspunkt';'Periode Tidspunkt'[Time] >= MaxTidspunkt)); Salg[Bogføringsdato] = MaxDato; CALCULATE(Salg[Salg Omsætning]; FILTER('Periode Tidspunkt';'Periode Tidspunkt'[Time] <= MAXTidspunktMaxDato)); Salg[Salg Omsætning])) ;1000)
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |