measure for a card

[IF]

Hi,

I have the following data at the Table.

Slider1	Slider2	Date	Value	DateFormated
#	A	07.2020	12	1/7/2020
#	B	07.2020	14	1/7/2020
C1	A	07.2020	21	1/7/2020
C2	A	07.2020	23	1/7/2020
D1	B	07.2020	32	1/7/2020
D2	B	07.2020	34	1/7/2020
#	A	08.2020	42	1/8/2020
#	B	08.2020	44	1/8/2020
C1	A	08.2020	55	1/8/2020
C2	A	08.2020	35	1/8/2020
D1	B	08.2020	46	1/8/2020
D2	B	08.2020	65	1/8/2020
#	A	11.2019	77	1/11/2019
#	B	11.2019	76	1/11/2019
C1	A	11.2019	54	1/11/2019
C2	A	11.2019	66	1/11/2019
D1	B	11.2019	56	1/11/2019
D2	B	11.2019	67	1/11/2019
#	#	07.2020	66	1/7/2020
#	#	08.2020	77	1/7/2020
#	#	11.2019	88	1/7/2020

I have also two more tables, calendar and Slicers tables. Calendar has relationship with Table[DateFormated], similarly I have another relationship between Slicers[Slider1] and Table[Slider1]. I have to keep this relationships for other calculations. I don’t want to change them since I may have problems with them as well.

I have three slicers; date, slider1 and slider2. I have to use the slicers from Calander[MonthYear], Slicers[Slider1], Slicers[Slider2] columns.

 If date is 07.2020, Slider1 is C1 (regardless Slider2=A selected or not), I want to see the result 21 at the measure.

If date is 07.2020, Slider1 not selected, and Slider2 is A, I want to see the result 12 at the measure.

If date is 07.2020, Slider1 and slider2 are not selected, the value should be 66.

How can I write a measure(s) to show the result in one card?

Thanks in advance!

[PaulDBrown]

@IF 

Can you please post the measure you are using currently? Also, you mention expected results, but what are you actually trying to calculate?

[IF]

I use the following measure:

Plan Working with V = VAR _slider1 = SELECTEDVALUE('Table'[Slider1]) RETURN IF(NOT(HASONEVALUE('Table'[Slider1])) && NOT(HASONEVALUE('Table'[Slider2])), CALCULATE(Sum('Table'[Value]), FILTER('Table', 'Table'[Slider1] = "#" && 'Table'[Slider2] = "#")), [Plan Working3] )

AND

Plan Working3 =

VAR _slider1 = SELECTEDVALUE('Table'[Slider1])

RETURN

IF(ISFILTERED('Table'[Slider1]),

CALCULATE(Sum('Table'[Value]), All('Table'[Slider2])),

CALCULATE(Sum('Table'[Value]), FILTER('Table', 'Table'[Slider1] = "#"))

)

My aim is to display the value of the table Table[Value] for each selected slicer. It should show the values of each column, no sum.

This measure works if there is no relationship between Slicers and Table tables. Also I have about 20 different tables similar to the posted table. Therefore I use slicer and calendar table to link them. It also works when I have two tables for slicer1 and slicer2. However, I have more than 100 relationships in the app and it started to stop while working with PBI desktop. I want to reduce the relationships. Therefore I want to combine slicer1 and slicer2 in one table and keep relationship through slicer1. Otherwise, the other calculations will not work.

All the best, 

[AlB]

Hi @IF 

Can you show the contents of the Slicer table (in tex-tabular format)? and the date table as well if it's not too large

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[IF]

The file is attached. Can you show how it can be done?

https://www.dropbox.com/s/zkycfgn2cu47gj0/Q2%20Target%20BEST2.pbix?dl=0

[AlB]

@IF 

I'm a bit lost. [Plan Working with V] actually yields the results you area talking about, 21, 12 and 66, doesn't it? I don't quite understand what the requirement is.  That with and without an active relationship between the Slicers table and Main Table. Plus you are using the columns in the Main table for the slicers? What is the point of the slicer table then?  In any case it doesn't sound like a good idea to have the columns of the slicers in the same table unless they are correlated, in which case you'd only need one column of the two to be used as slicer

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers -

[IF]

It works because the slicers are from Table[Slider1] and Table[Slider2]. I want to use Slicers[Slider1] and Slicers[Slider2] columns because I have some other tables and I want to link them. I use the data from Table[value] and I have more tables in this structure. I get only data from them and use the slider table to show the values with different cards. I hope it is clear now. 

[Anonymous · Accepted solution]

Hi @IF ,

I updated the following information in your sample pbix file, please check whether that(see the attachment) is what you want.

1. Use the field Slider 1 and Slider 2 of table Slicers

2. Update the formula of measure [Plan Working3] and [Plan Working with V] as below

00_Plan Working3 = 
VAR _slider1 =
    SELECTEDVALUE ( 'Slicers'[Slider1] )
RETURN
    IF (
        ISFILTERED ( 'Slicers'[Slider1] ),
        CALCULATE ( SUM ( 'Table'[Value] ), ALL ( 'Table'[Slider2] ) ),
        IF (
            ISFILTERED ( 'Slicers'[Slider2] ),
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER (
                  ALL( 'Table') ,
                    'Table'[Slider1] = "#"
                        && 'Table'[Slider2] = SELECTEDVALUE ( 'Slicers'[Slider2] )
                        && 'Table'[Date] = SELECTEDVALUE ( 'Table'[Date] )
                )
            ),
            CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', 'Table'[Slider1] = "#" ) )
        )
    )

00_Plan Working with V = 
VAR _slider1 =
    SELECTEDVALUE ( 'Slicers'[Slider1] )
RETURN
    IF (
        NOT ( HASONEVALUE ( 'Slicers'[Slider1] ) )
            && NOT ( HASONEVALUE ( 'Slicers'[Slider2] ) ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( 'Table', 'Table'[Slider1] = "#" && 'Table'[Slider2] = "#" )
        ),
        [00_Plan Working3]
    ) 

Best Regards
Rena
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[IF]

Hi Rena,

I wanted to try it with the real scneario before answering it. It works very well. Thank you very much.

All the best,

p.s. Hi, all. If you want to produce a report by using plan data, this is a very good example. I think all SAP plan data is using similar source. You can use this logic. wonderful!