crossfiltering on a higher level....

[Stevianne]

Hello PBI community,

I created below DAX formula for six different municipalities. A, B, C, D, E and XY in order to benchmark data.

XY =

VAR Gerealiseerd = CALCULATE( SUM( Zorggegevens[bedrag_goedgekeurd] ) ,Persoonsgegevens[Gemeente]= "XY" )

VAR Benchmark =

CALCULATE( SUM( Zorggegevens[bedrag_goedgekeurd]) ,

Persoonsgegevens[Gemeente] = "XY" ,

FILTER( ALL(Datumtabel) ,

Datumtabel[JaarKwartaal] = "2018Q1" )

)

RETURN

DIVIDE( Gerealiseerd - Benchmark , Benchmark)

When I add these six DAX formulas in a line chart I get below:

So far so good! However, When I add a slicer   

With informatino from "percelen" and "productcodes" to benchmark information between different municipalities. This information does not appear in the line chart. Because the municipalities (in dutch 'gemeente') has no relation with tables Percelen and productcodes.

What do I have to do so information about benchmarking will appear in the line chart.

[PaulDBrown]

Try:

XY =
VAR Gerealiseerd =
    CALCULATE (
        SUM ( Zorggegevens[bedrag_goedgekeurd] ),
        FILTER ( Persoonsgegevens, Persoonsgegevens[Gemeente] = "XY" )
    )
VAR Benchmark =
    CALCULATE (
        SUM ( Zorggegevens[bedrag_goedgekeurd] ),
        FILTER ( Persoonsgegevens, Persoonsgegevens[Gemeente] = "XY" ),
        FILTER ( ALL ( Datumtabel ), Datumtabel[JaarKwartaal] = "2018Q1" )
    )
RETURN
    DIVIDE ( Gerealiseerd - Benchmark, Benchmark )

[Stevianne]

Hi,

I did apply your DAX formula. Unfortunately when I filter on a specific 'perceel' No information appears in the line chart...

[PaulDBrown]

It's hard to see what's going on without sample data or PBIX file. Any chance you can share via a cloud service?

[Stevianne]

Yes I understand. Unfortunately I'am not allowed to share data because of privacy. Anyway I can explain in detail which steps I took or I make an duplicate of this model with different information.

What do you prefer?

[PaulDBrown]

If you could create a dummy PBIX file it would be most helpful!

[Stevianne]

I will do that. Takes me some time, hope this week!