bypass a slicer

[yossil]

Hi 

i'm reposting the issue, this time with a simple example.

I have table with column "Cycle" represent the reporting cycle (Quick report or Final report)

And other columns like "month" (as number) "year" (as number) "amount" etc..

If I'm going to use the slicer for "cycle" it will show me all the data for 'Quick report' or for 'Final report'

What I'm actually need, is:

1. if we choose anything but 'Quick report' go by the slicer
2. And if we choose 'Quick report' take all the months before last month as 'Final report'  and just for last month show me data from 'Quick report'.

anyone have an idea how can i achieve that goal?

(i've tried to disconnect the slicer from a relationship with the Datatable, i didn't succeed)

[v-chuncz-msft · Accepted solution]

@yossil,

You may try the measure below.

Measure =
VAR c =
    SELECTEDVALUE ( 'Table2'[cycle] )
VAR m =
    CALCULATE ( MAX ( Table1[month] ), ALLSELECTED ( Table1[month] ) )
RETURN
    SUMX (
        FILTER (
            Table1,
            SWITCH (
                c,
                "Quick", ( Table1[cycle] = "Final"
                    && Table1[month] < m )
                    || ( Table1[cycle] = c
                    && Table1[month] = m ),
                Table1[cycle] IN VALUES ( Table2[cycle] )
            )
        ),
        Table1[amount]
    )

[yossil]

Hi sam (@v-chuncz-msft)

First of all, thank you very much for the answer, this is definitely the solution for the problem I raised.

An interesting anecdote I thought to share with you is, that when I initially tried to use the solution you wrote (and similar solutions with minor changes that I wrote myself, attached at the end) it didn't worked.

i.e. the end result of each year was presented correctly, but the amount showed of each months did not match what I expected it to be.

In order to answer you that did not solve the problem, I prepared a small dataset containing the formula you wrote with some sample data to share and try to understand why despite the logic in the formula, something does not work.

Surprisingly, when I created the dataset to show that it isn't working, I found that it's working.

Then I've tried to understand the difference between the dataset I just wrote and the dataset I already had and find the differences between them.

To make a long story short, the issue is Month name (instead of months number)

In my original dataset, the months (in the columns) appear in their names, whereas in the dataset I prepared as example, the months (in the columns) appear in their numbers. It appears that when the months are presented by numbers, they work correctly, whereas when presented by their names (although they are linked with proper relationship) It goes wrong.

Anyway, for the current problem I raised, as mentioned, indeed your answer provides an appropriate solution and therefore I've marked it as an acceptable solution.

As a sub-question, I wonder if anyone has come across this matter of the months and if anyone have an idea how to overcome this problem.

Includes:

1.  screenshots of the different results according to the months in the columns (the top table is the correct one and the bottom is correct only in total but not in months).

2. the final code for the original problem

3. for those who are interested, attaching the entire project.

Thanks.

Yossi.

SampleDataset

Combined Cycles Rooms sales = 
VAR cycle = SELECTEDVALUE ( Reporting_cycle[cycle_id] )
VAR Month = CALCULATE ( MAX ( Months[months_id] ), ALLSELECTED ( Months[months_id] ) )
VAR Year = CALCULATE ( MAX ( Years[years_number] ), ALLSELECTED ( Years[years_number] ) )
RETURN
    SUMX (
        FILTER (
            CalculatedReport,
            SWITCH (
                cycle, 6,( CalculatedReport[Cycle] = 7 && CalculatedReport[Year]< (Year-2000) )
                    ||( CalculatedReport[Cycle] = 7 && CalculatedReport[Year]= (Year-2000) && CalculatedReport[Month] < Month )                    
                    || ( CalculatedReport[Cycle] = cycle && CalculatedReport[Year]= (Year-2000) && CalculatedReport[Month] = Month ),
                7,CalculatedReport[Cycle] =7,
                1,CalculatedReport[Cycle] =1 )
        ),
        [Rooms sales]
    )

[chrispybacon]

Go to the query editor ==> Transform ==> Pivot column.

Pivot your cycle column, splitting the single attribute values into two columns, effectivley doubling the length of your table. Then create two tables with the different columns. Also when you have the slicer slected you can go to format and "Edit Interactions". There you can set which visual will be affected by the currently selected slicer. 

BR,
Chris

[yossil]

Hi @chrispybacon

thanks for you're answer.

but this is not a realistic solution when you have billions of records and not just 2 cycles

i've simplified the problem in order to make it more understandable.

and about "Edit interactions", i DO want the slicer to effect on ths outcome, but not in the usual way.

Thanks anyway 🙂