Forum Discussion

Giovani's avatar
Giovani
Icon for Helper I rankHelper I
5 years ago
Solved

Dynamic filter based in multiple columns and criteria

I am struggling with something that may be simple to solve. By year and month, I want to make a dynamic bar chart to present counts by a distinct ID that fits criteria based on columns Filter1 and Fi...
Need Help DAX
Need Help DAX measure
  • wdx223_Daniel's avatar
    wdx223_Daniel
    5 years ago

    Giovani might these code help

     

     

    ID_Count:=COUNTROWS (
    FILTER (
        VALUES ( SampleData[ID] ),
        VAR _Filter1 =
            CALCULATETABLE (
                VALUES ( SampleData[Filter1] ),
                ALLEXCEPT ( SampleData, SampleData[ID] )
            )
        RETURN
            AND ( CONTAINSROW ( _Filter1, "A" ), CONTAINSROW ( _Filter1, "B" ) )
    )
)
----------------------------
ID_Count2:=COUNTROWS (
    FILTER (
        VALUES ( SampleData[ID] ),
        VAR _Filter1 =
            CALCULATETABLE (
                SUMMARIZE ( SampleData, SampleData[Filter1], SampleData[Filter2] ),
                ALLEXCEPT ( SampleData, SampleData[ID] )
            )
        RETURN
            AND ( CONTAINSROW ( _Filter1, "A", "D" ), CONTAINSROW ( _Filter1, "B", "D" ) )
    )
)
----------------------
ID_Count3:=COUNTROWS (
    FILTER (
        VALUES ( SampleData[ID] ),
        VAR _Filter1 =
            CALCULATETABLE (
                SUMMARIZE ( SampleData, SampleData[Filter1], SampleData[Filter2] ),
                ALLEXCEPT ( SampleData, SampleData[ID] )
            )
        RETURN
            AND ( CONTAINSROW ( _Filter1, "A", "D" ), CONTAINSROW ( _Filter1, "B", "E" ) )
    )
)

     

     

     

wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
5 years ago

Giovani might these code help

 

 

ID_Count:=COUNTROWS (
    FILTER (
        VALUES ( SampleData[ID] ),
        VAR _Filter1 =
            CALCULATETABLE (
                VALUES ( SampleData[Filter1] ),
                ALLEXCEPT ( SampleData, SampleData[ID] )
            )
        RETURN
            AND ( CONTAINSROW ( _Filter1, "A" ), CONTAINSROW ( _Filter1, "B" ) )
    )
)
----------------------------
ID_Count2:=COUNTROWS (
    FILTER (
        VALUES ( SampleData[ID] ),
        VAR _Filter1 =
            CALCULATETABLE (
                SUMMARIZE ( SampleData, SampleData[Filter1], SampleData[Filter2] ),
                ALLEXCEPT ( SampleData, SampleData[ID] )
            )
        RETURN
            AND ( CONTAINSROW ( _Filter1, "A", "D" ), CONTAINSROW ( _Filter1, "B", "D" ) )
    )
)
----------------------
ID_Count3:=COUNTROWS (
    FILTER (
        VALUES ( SampleData[ID] ),
        VAR _Filter1 =
            CALCULATETABLE (
                SUMMARIZE ( SampleData, SampleData[Filter1], SampleData[Filter2] ),
                ALLEXCEPT ( SampleData, SampleData[ID] )
            )
        RETURN
            AND ( CONTAINSROW ( _Filter1, "A", "D" ), CONTAINSROW ( _Filter1, "B", "E" ) )
    )
)

 

 

 

Giovani's avatar
Giovani
Icon for Helper I rankHelper I
5 years ago

Many thanks to wdx223_Daniel this is perfect and is exactlly what I was looking for!