topic Re: Dax formula in DAX Commands and Tips

Dax formula

zameenakarmali — Tue, 08 Oct 2024 11:55:51 GMT

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

Re: Dax formula

bhanu_gautam — Tue, 08 Oct 2024 12:35:58 GMT

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")

Re: Dax formula

zameenakarmali — Tue, 08 Oct 2024 13:15:08 GMT

Thanks for the suggestion but this still didnt seem to work

Re: Dax formula

Anonymous — Thu, 10 Oct 2024 09:11:50 GMT

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.

Re: Dax formula

zameenakarmali — Thu, 10 Oct 2024 11:11:14 GMT

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?

Re: Dax formula

zameenakarmali — Fri, 11 Oct 2024 16:48:19 GMT

Thanks, can you let me know how to upload a power bi file?

Re: Dax formula

Anonymous — Mon, 14 Oct 2024 01:21:22 GMT

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

Re: Dax formula

powerbiuser1111 — Mon, 14 Oct 2024 15:49:15 GMT

Hi Zhu,

Please find a link to the file

https://drive.google.com/file/d/17N0nK-tIUTEKVss5hRIMk8JYO5NniFKQ/view?usp=drive_link

Thanks,

Re: Dax formula

Anonymous — Tue, 15 Oct 2024 05:46:20 GMT

Hi powerbiuser1111 ,

You may need to reset it again, I don't have permission to open your file. Thank you in advance for your efforts.

Best Regards,
Zhu

Re: Dax formula

powerbiuser1111 — Tue, 15 Oct 2024 12:37:35 GMT

Can you try this link:

Test File.pbix

Thanks so much

Re: Dax formula

Anonymous — Wed, 16 Oct 2024 05:48:00 GMT

Hi powerbiuser1111 ,

Please refer to the following steps:

1.Create a new table, use it as a slicer field:

2.Create the relationships:

3.Create new measures:

Selected Quarter2 = LOOKUPVALUE('Dim_Date'[Qtr No],'Dim_Date'[Quarter],MAX('Quarter'[Quarter]))

Active/Expired2 = IF(ISINSCOPE('Quarter'[Quarter]),IF([Selected Quarter2]<CALCULATE(MAX('Table'[End Quarter Number]),REMOVEFILTERS('Dim_Date')),"Active","Expired"))

4.The result is as follows:

By the way, If you want to compare Starting Quarter and End Quarter in your table you can create two more calculated columns:

Start Quarter Number = LOOKUPVALUE('Dim_Date'[Qtr No],'Dim_Date'[Quarter],'Table'[Starting Quarter])

Active/Expired3 = IF('Table'[Start Quarter Number]<'Table'[End Quarter Number],"Active","Expired")

Result:

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.

Re: Dax formula

zameenakarmali — Thu, 17 Oct 2024 11:34:15 GMT

Thanks for that,

If I also have a Sales amount for each contract, and I would like to show just the Sum of Sales for Active and Expired Contracts, how would I do that?

Re: Dax formula

Anonymous — Fri, 18 Oct 2024 02:33:02 GMT

Hi zameenakarmali ,

As shown in the image, I added two columns of Contract ID as well as Sales to the original Table:

Then create a measure, use the ALL function to remove all filters to get all active and expired contract sales:

Salesamount = CALCULATE(SUM('Table'[Sales]),ALL('Table'))

The result is as follows:

Best Regards,
Zhu

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

Re: Dax formula

zameenakarmali — Fri, 18 Oct 2024 09:11:33 GMT

If I was only to show the Sales Amount and the Active/Expired column would that work?

Re: Dax formula

zameenakarmali — Mon, 21 Oct 2024 13:48:27 GMT

I would like to show a table like below:

The sales amount would change depending on the Selected value of the Quarter.

Re: Dax formula

Anonymous — Wed, 23 Oct 2024 07:38:33 GMT

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.