topic DAX help in DAX Commands and Tips

DAX help

prashantg364 — Fri, 10 Jan 2025 08:01:01 GMT

Original data table has 3 columns - SiteID, time, IFHO_INTRA.
need to calculate the number of days IFHO_INTRA >=98 under a new column "Pass days"
the below table shows data table filtered from 6 Jan to 9 Jan and based on the date selection Pass days column created manually.

Example:
SiteID 3803, having IFHO_INTRA >=98 for all 4 days from the filetered days

SiteID 3810, having IFHO_INTRA >=98 for 3 days from the filetered days

SiteID 3869, having IFHO_INTRA >=98 for 3 days from the filetered days and for 6 Jan Data is blank, so consider it as fail. Blank data can be considered as fail.
SiteID 3873, having IFHO_INTRA >=98 for 0 days from the filetered days

How can i do it with DAX column, so that my Pass days changes based on the selected dates and IFHO_INTRA >=98.
Infact i want to do this only for last 5 dates in my data set.

SiteID	time	IFHO_INTRA	Pass days
3803	6-1-25	98.83	4
3803	7-1-25	99.48	4
3803	8-1-25	100.00	4
3803	9-1-25	98.70	4
3810	6-1-25	98.95	3
3810	7-1-25	97.12	3
3810	8-1-25	98.39	3
3810	9-1-25	97.78	3
3835	6-1-25	99.01	4
3835	7-1-25	98.92	4
3835	8-1-25	99.22	4
3835	9-1-25	99.08	4
3869	6-1-25		3
3869	7-1-25	99.59	3
3869	8-1-25	99.74	3
3869	9-1-25	99.45	3
3873	6-1-25	97.60	0
3873	7-1-25	97.87	0
3873	8-1-25	96.73	0
3873	9-1-25	97.37	0
3946	6-1-25	97.57	0
3946	7-1-25	92.69	0
3946	8-1-25	96.29	0
3946	9-1-25	93.96	0
3950	6-1-25	89.78	1
3950	7-1-25	93.85	1
3950	8-1-25	92.83	1
3950	9-1-25	99.05	1
3954	6-1-25	94.98	0
3954	7-1-25	96.87	0
3954	8-1-25	96.19	0
3954	9-1-25	96.65	0
4008	6-1-25	97.56	3
4008	7-1-25	99.35	3
4008	8-1-25	99.13	3
4008	9-1-25	99.22	3
4012	6-1-25	99.54	2
4012	7-1-25	99.77	2
4012	8-1-25	97.77	2
4012	9-1-25	97.85	2

Re: DAX help

johnt75 — Fri, 10 Jan 2025 09:28:59 GMT

You can create a calculated column like

Pass Days = VAR Last5Days = WINDOW ( 1, ABS, 5, ABS, DISTINCT ( 'Table'[time] ), ORDERBY ( 'Table'[time], DESC ) ) VAR Result = CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[SiteID] ), Last5Days, 'Table'[IFHO_INTRA] >= 98 ) RETURN Result

Re: DAX help

Poojara_D12 — Fri, 10 Jan 2025 10:09:48 GMT

To calculate "Pass Days" (days where IFHO_INTRA >= 98 for each SiteID) dynamically in Power BI:

Dynamic Measure

Use this measure for dynamic filtering (e.g., last 5 days):

Pass Days = VAR Last5Dates = TOPN(5, ALL('YourTable'[time]), 'YourTable'[time], DESC) // Get last 5 dates VAR FilteredTable = FILTER( 'YourTable', 'YourTable'[time] IN Last5Dates && 'YourTable'[IFHO_INTRA] >= 98 ) RETURN CALCULATE( COUNTROWS(FILTEREDTABLE), DISTINCT('YourTable'[SiteID]) )

Static Column

If a static result is needed, use this calculated column:

Pass Days = CALCULATE( COUNT('YourTable'[time]), FILTER( 'YourTable', 'YourTable'[SiteID] = EARLIER('YourTable'[SiteID]) && 'YourTable'[IFHO_INTRA] >= 98 ) )

Logic

1. Measure dynamically calculates "Pass Days" for the last 5 dates.

2. Column calculates it statically for all rows in the dataset.

3. Blanks in IFHO_INTRA are treated as fails (< 98).

This ensures the "Pass Days" dynamically updates based on the date filter or slicer.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

Re: DAX help

sanalytics — Fri, 10 Jan 2025 10:14:52 GMT

prashantg364
i) How come 3810 will be 2? it should be 2. Check your data once again.
ii) It is not possible to dynamically apply slicer selections to a calculated column. Slicers impact visuals by defining the filter context, whereas calculated columns are evaluated during data refresh. both are different concepts.
if you want to have slicer impact on your table then suggesting you create measure instead of calculated column.
For measure you can refer below code

Pass days= VAR _1 = CALCULATE( COUNTROWS( FILTER( 'Table',[Total Intra] > 98 )) , ALLSELECTED( 'Table'[time] ) ) VAR _Result = IF( ISBLANK( _1 ),0, _1 ) RETURN _Result

below screenshot

For calculated column, you will have to manually select the last five days and then create the calculated column which johnt75 has provided the solution.

let me know if you need pbix file.

Regards

sanalytics