Forum Discussion

prashantg364's avatar
prashantg364
Helper II
1 year ago
Solved

DAX help

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 Ja...
  • sanalytics's avatar
    sanalytics
    1 year ago

    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

     

     



Poojara_D12's avatar
Poojara_D12
Super User
1 year ago

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