Forum Discussion

zameenakarmali's avatar
zameenakarmali
Icon for Helper I rankHelper I
1 year ago
Solved

Dax formula

I have the following column I have created in Power BI: 

Active & Expired =
VAR SelectedQuarter = SELECTEDVALUE('Calender'[Quarter])
VAR SelectedQtrNo = LOOKUPVALUE('Calender'[Qtr No], 'Calender'[Quarter], SelectedQuarter)
VAR EndQtrNo = LOOKUPVALUE('Calender'[Qtr No], 'Calender'[Quarter], 'Subscriptions Register'[End Quarter Value])

RETURN
    IF(EndQtrNo > SelectedQtrNo, "Active","Expired")

The Active & Expired column only returns the Value for Active when it should have both? 

I tried debugging the measure and cant seem to find the issue

 

 

 

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi zameenakarmali ,

     

    Since you only want to present these two columns, but in the previous reply Active/Expired was a measure, this would be difficult to achieve out of context, so I'll provide you with an alternative by recreating an Active/Expired calculated column.


    To do this, I've made some changes to the original data:

    1. Added a Sales column to Table.

    2. A new table named Status has been created:

     

    Then here are the specific steps:

    1. Generate a new table, Table 2, which is the Cartesian product of the tables Table and Quarter.

     

    Table 2 = CROSSJOIN('Table','Quarter')

     

     

    2. Create two new columns for Table 2.

    QuarterNumber = LOOKUPVALUE(Dim_Date[Qtr No],Dim_Date[Quarter],'Table 2'[Quarter])

     

    FinalActive/Expired3 = IF('Table 2'[QuarterNumber]>'Table 2'[End Quarter Number],"Active","Expired")

     

     

    3. Create a new relationship:

    4. Create a measure:

     

    Salesvalue = IF(SUM('Table 2'[Sales])=BLANK(),0,IF(HASONEVALUE('Table 2'[Quarter]),SUM('Table 2'[Sales])))

     

     

    5. Create a slicer using the field Quarter from Table 2 and create a table using the field from Status and Salesvalue.

    Best Regards,
    Zhu
    Community Support Team

     

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

15 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    zameenakarmali , Try using below DAX

     

    Active & Expired =
    VAR SelectedQuarter = SELECTEDVALUE('Calender'[Quarter])
    VAR SelectedQtrNo = LOOKUPVALUE('Calender'[Qtr No], 'Calender'[Quarter], SelectedQuarter)
    VAR EndQtrNo = CALCULATE(
    LOOKUPVALUE('Calender'[Qtr No], 'Calender'[Quarter], 'Subscriptions Register'[End Quarter Value])
    )
    RETURN
    IF(EndQtrNo > SelectedQtrNo, "Active", "Expired")

    • zameenakarmali's avatar
      zameenakarmali
      Icon for Helper I rankHelper I
      1 year ago

      Thanks for this, it still doesnt work, 

       

      I am assuming the issue is because the End Quarter Value is a column field and the selected value is a measure that only has one value?

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the reply from bhanu_gautam.

     

    Hi zameenakarmali ,

     

    Please try the following DAX:

    Active & Expired =
VAR SelectedQuarter =
    MAX ( 'Calender'[Quarter] )
VAR SelectedQtrNo =
    LOOKUPVALUE ( 'Calender'[Qtr No], 'Calender'[Quarter], SelectedQuarter )
VAR EndQtrNo =
    LOOKUPVALUE (
        'Calender'[Qtr No],
        'Calender'[Quarter], 'Subscriptions Register'[End Quarter Value]
    )
RETURN
    IF ( EndQtrNo > SelectedQtrNo, "Active", "Expired" )

     

    If the problem persists, can you provide a .pbix file with sensitive data removed? This will help locate your problem faster.

     

    Best Regards,
    Zhu
    Community Support Team

     

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

     

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi zameenakarmali ,


        you need to upload your file to a cloud storage (like OneDrive, Dropbox, Google Drive or Wetransfer for example) and paste the link to that storage location in the post. A friendly reminder to remember to protect your data privacy.

         

        Best Regards,
        Zhu