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 :       ...
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

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