Forum Discussion

Alde's avatar
Alde
Frequent Visitor
4 years ago
Solved

Show a specific column data When no filter is selected

Hi everyone    I'm using this DAX to show a column value from "FEB" with a measure, but I have many measures to adjust every month, instead I want to use a predefied measure "CurrentMonth" to use ...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Alde , 

    I have created a simple sample, please refer to my pbix file to see if it helps you.

    Create a measure.

    Measure = IF(ISFILTERED('Table'[month])=TRUE(),FIRSTNONBLANK('Table'[value%],1),CALCULATE(SUM('Table'[value%]),'Table'[month]=SELECTEDVALUE('Table (2)'[date])))

     

    What cause your error?

    The following comparisons are not supported:

      1. Comparing to a column to a measure. SalesHeader[TerritoryID] = [LargestTerritory]
      2. Comparing a column to a an aggregate value. SalesHeader[TerritoryID] = MAX(TerritoryID[TerritoryID]])
      3. Comparing a column to a What-If parameter. SalesHeader[TerritoryID] =TerritoryParameter[TerritoryParameter Value]

    More details: DAX error: A function ‘XXXX’ has been used in a True/False expression that is used as a table filter expression. This is not allowed. 

     

    If I have misunderstood your meaning, please provide your pbi file without privacy information and your desired output.

     

    Best Regards

    Community Support Team _ Polly

     

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

     

     

     

Alde's avatar
Alde
Frequent Visitor
4 years ago

Hi Anonymous 

 

Thanks for the sample, it did work until 2nd level of nested DAX,

 

When I tried this one, It doesn't work anymore at 3rd level while I'm trying to create a clustered stacked bar, but it works if I just use a value "Value" instead of SELECTEDVALUE().

 

Do you have any idea? 🥹 

Thank youuuuuu

BUD_OB2 = 
SUMX('Col_Type',
VAR CurrentType = 'Col_Type'[Col_Type]
    Return if
        (CurrentType="OB YEAR BUDGET",
        if (ISFILTERED('ORDER_TABLE'[MONTH])=truE(), FIRSTNONBLANK('FCST_BUD'[OB (start of month) BUD FY 42],1),
        CALCULATE(Sum('FCST_BUD'[OB (start of month) bud FY 42]), 'FCST_BUD'[Month] = SELECTEDVALUE(Col_Type[CurrentMonth]))
            )
        )
    )

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Alde ,

I cannot reproduce your issue. Could you please provide your pbix file without privacy information and desired output?  If impossible, colud you please provide enough sample data to reproduce the issue?

 

Best Regards

Community Support Team _ Polly

 

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

  • Alde's avatar
    Alde
    Frequent Visitor
    4 years ago

    Hi Anonymous 

    Here's the PBI sample file and attached an image for my idea.

    Please help, thank youuuuu <33

    https://drive.google.com/file/d/128YEVdMEsiBYfMy-R6J4SowibiA-sXoC/view?usp=sharing

     

    OBActual_OB = 
SUMX('Col_Type',
VAR CurrentType = 'Col_Type'[Col_Type]
    Return
            if
            (CurrentType="OB Actual",
            IF  
            (ISFILTERED('ORDER_TABLE'[MONTH])=False(),
            CALCULATE(Sum('obcb'[OB Actual]), 'obcb'[Month] = "mar"),
            FIRSTNONBLANK('obcb'[OB Actual],1)
            )
            )
        )

     

    • Alde's avatar
      Alde
      Frequent Visitor
      4 years ago

      I'd like to use data from 'Col-Type'[Current_Month) in stead of typing "Mar" in the measure, cuz there's be a few dozens of them to use, I can't update all of them every month. Thank youu.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Alde ,

      I don't have access so I can't get your pbix file.Could you please provide again or use another way?

       

       

      Best Regards

      Community Support Team _ Polly

       

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

       

      • Alde's avatar
        Alde
        Frequent Visitor
        4 years ago

        Anonymous 

        Finally I got the solution, it didn't work because I put the CurrentMonth column in the same table of the month used to check against, now i created another table without making any relationship and use it in the conditional query. 

        OBActual_OB = 
SUMX('Col_Type',
            if
            ('Col_Type'[Col_Type]="OB Actual",
            IF  
            (ISFILTERED('ORDER_TABLE'[MONTH])=True(),FIRSTNONBLANK('obcb'[OB Actual],1),
            CALCULATE(Sum('obcb'[OB Actual]),'obcb'[Month]= SELECTEDVALUE(lastdatamonth[LastMonth]))
            )
        ))