Slicer values should be filteted based on selected values (disconneted tables)

[hanuraolm]

Sample data as below - 

Build	Category	PRNumber	Seq
b1	Cat1	PR1010	1
b2	Cat2	PR1022	2
b3	Cat3	PR10333	3
b4	Cat1	PR10444	4
b5	Cat2	PR1055	5
b6	Cat3	PR4567	6

Req –

In the above table we have a build column with text attribute and users need to capability to choose the fromBuild and toBuild. 

To achieve this we a created a sequence col based on build

Two disconnected tables based on build 1.FromBuild 2.ToBuild

Created a measure as below –

               # Of PRs =

CALCULATE (

    DISTINCTCOUNT (build[PRNumber] ),

    FILTER (

        build,

        (build[Seq] >= 5  && build[Seq]<=6)))

5,6 are hardcoded but these values are passing from disconnected tables. Now no issues every thing working as expected.

Now the problem is, if we need to create a slicer based on Category (highlited below) from build table and these values should be filtered between the selected fromBuild and toBuild values. This I couldn’t able to achieve this. Can you help on this.

[v-xinruzhu-msft · Accepted solution]

Hi @hanuraolm 

You can refer to the following measure, it does not need to create the seq column

# Of PRs =
IF (
    ISFILTERED ( frombuild[Build] ) && ISFILTERED ( frombuild[Build] ),
    CALCULATE (
        DISTINCTCOUNT ( build[PRNumber] ),
        FILTER (
            build,
            [Build] >= SELECTEDVALUE ( frombuild[Build] )
                && [Build] <= SELECTEDVALUE ( tobuild[Build] )
        )
    ),
    CALCULATE (
        DISTINCTCOUNT ( build[PRNumber] ),
        FILTER ( build, [Build] >= "b5" && [Build] <= "b6" )
    )
)

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.