Forum Discussion

AliceFGX's avatar
AliceFGX
Frequent Visitor
3 years ago
Solved

How to Exclude a company from a visual when data is not available every month?

Hello,

I have a powerBI report where I show on the rows the company names and on columns the months, like below. Some companies like company B do not send me the overdues data every month :

 

                     JAN FEB MAR JUN JUL

Company A  10    15    30    20   22

Company B   5      8                    10

 

I have a slicer that allows to show any months since jan 2021, here I selected only JAN to JUL 2023.

I want to have another slicer that alows me to exclude the companies that did not send data on the period shown on the table, and show only the company A and the others but not B.   So if I select only JAN 2023 and FEB 2023, company B should appear as well as A, as I have received the data for these months in 2023.

 

How can I do this? Thanks in advance to this great community!

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi AliceFGX ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    Flag = 
VAR _company =
    SELECTEDVALUE ( 'Table'[Company] )
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Month] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Company] = _company )
    )
VAR _tab =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        'Table'[Company],
        'Table'[Month],
        "@count",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Month] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Company] = EARLIER ( 'Table'[Company] )
                )
            )
    )
RETURN
    IF ( _count < MAXX ( _tab, [@count] ), 0, 1 )

    Best Regards

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi AliceFGX ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    Flag = 
VAR _company =
    SELECTEDVALUE ( 'Table'[Company] )
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Month] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Company] = _company )
    )
VAR _tab =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        'Table'[Company],
        'Table'[Month],
        "@count",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Month] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Company] = EARLIER ( 'Table'[Company] )
                )
            )
    )
RETURN
    IF ( _count < MAXX ( _tab, [@count] ), 0, 1 )

    Best Regards