Forum Discussion

Prakash1050's avatar
Prakash1050
Helper III
1 year ago
Solved

Background Color in Matrix rows

Hi All,       I have one matrix visual.  That matrix row have : Table 1 [Details] That matrix column have : Calender[Month_Year_Format] That matrix value have : one measure[Overall_Measure] it re...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Prakash1050 ,
    If you want to automatically determine if a month is legal, you can create two columns on the table

    IsNotBlank = 
IF(
    'Table 2'[Value] <> BLANK(),
    1,
    0
)

    IsTrue = 
CALCULATE(
    SUM('Table 2'[IsNotBlank]),
    ALLEXCEPT(
        'Table 2',
        'Table 2'[Month_Year]
    )
)

    Create a column on the calendar table

    IsExist = 
VAR CurrentMonthYear = 'Calendar'[Calendar]
VAR _isTrue =
IF (
    ISBLANK (
        LOOKUPVALUE (
            'Table 2'[IsTrue],
            'Table 2'[Month_Year], CurrentMonthYear
        )
    ),
    0,
    LOOKUPVALUE (
        'Table 2'[IsTrue],
        'Table 2'[Month_Year], CurrentMonthYear
    )
)
RETURN
IF(
    _isTrue > 0,
    1,
    0
)

    Putting isExist into the visualization filter

    Set is 1 and final output

     

    Best regards,
    Albert He

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

     



     

Prakash1050's avatar
Prakash1050
Helper III
1 year ago

Yes correct Anonymous ,

But, the data will be added regularly. So, i need check each time when the data will be added or not and I change the filter selection on each time.

Is any other way to achieve this logic?

Sorry for adding my doubts again and again.

 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Prakash1050 ,
If you want to automatically determine if a month is legal, you can create two columns on the table

IsNotBlank = 
IF(
    'Table 2'[Value] <> BLANK(),
    1,
    0
)

IsTrue = 
CALCULATE(
    SUM('Table 2'[IsNotBlank]),
    ALLEXCEPT(
        'Table 2',
        'Table 2'[Month_Year]
    )
)

Create a column on the calendar table

IsExist = 
VAR CurrentMonthYear = 'Calendar'[Calendar]
VAR _isTrue =
IF (
    ISBLANK (
        LOOKUPVALUE (
            'Table 2'[IsTrue],
            'Table 2'[Month_Year], CurrentMonthYear
        )
    ),
    0,
    LOOKUPVALUE (
        'Table 2'[IsTrue],
        'Table 2'[Month_Year], CurrentMonthYear
    )
)
RETURN
IF(
    _isTrue > 0,
    1,
    0
)

Putting isExist into the visualization filter

Set is 1 and final output

 

Best regards,
Albert He

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

 



 

  • Prakash1050's avatar
    Prakash1050
    Helper III
    1 year ago

    Hi Anonymous ,

        Thank you for this much effort. 
        It fullfil my requirement.

        Sorry, for this much days delayed.

        This is I need. Thank you once again.