Forum Discussion

nit101's avatar
nit101
Frequent Visitor
2 years ago

Calculating total from related dataset

Hi,

I’m learning Power BI and I can’t work out how to get total from one of my datasets. 

I have 2 sliders – date uses vCalendar dataset and programme names uses vProgrammes dataset. 

My main report contains data from vPivotAttendance-Participant. 

Table 1 below shows the filter values I want to use in the measure.

I need to calculate the total number of days for each programme from vProgrammeDelivery that matches programme in vPivotAttendance-Participant.

Table 2 below shows the ProgSessionDay I want.

 

ProgSessionDay = COUNTROWS(VALUES(vProgrammeDeliveryDates[StartDate]))

 

When I add the measure in table 3, it show the Total for all participants ignoring the filters, so brings up all programmes.

This measure gives me the grand total from table 2 column ProgSessionDay. :

var _days =    

    CountX(

       KEEPFILTERS(VALUES(vProgrammeDeliveryDates[StartDate] ))

       , [ProgFilter]

    )

 

Please could someone help me work this out.

Thanks

 

Need Help

8 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    your data model looks reasonable.  Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information or anything not related to the issue or question.

    If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi nit101 ,

    Please try this DAX:

    TotalProgramDays = CALCULATE(
    COUNTROWS(VALUES(vProgrammeDeliveryDates[StartDate])),
    FILTER(
        ALL(vProgrammeDeliveryDates),
        vProgrammeDeliveryDates[ProgrammeName] IN VALUES(vPivotAttendance-Participant[ProgrammeName])
    )
)

    But as lbendlin  says, please provide sample data, which will be helpful!

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

  • nit101's avatar
    nit101
    Frequent Visitor
    2 years ago

    Hi Anonymous Dino,

     

    Sorry for the delay to your response. Your Dax is nearly what I what, it counts the total number of dates for a ProgrammeShortName, but I also need the date filter from the Slider.  I thought this might work but it is not filtering on date:

     

    TotalProgramDays =
        CALCULATE(
            COUNTROWS(VALUES(vProgrammeDeliveryDates[StartDate])),
            FILTER(
                ALL(vProgrammeDeliveryDates),
                vProgrammeDeliveryDates[ProgrammeShortName] = ('vPivotAttendance-Participant'[PSN_ParticipantSingle])
                && vProgrammeDeliveryDates[StartDate] >= MINX( 'vCalendar', vCalendar[TheDate] )
                && vProgrammeDeliveryDates[StartDate] <= MAXX( 'vCalendar', vCalendar[TheDate] )
            )
        )
     
    vCalendar is a standard date dataset that lists all dates for the slider.
     
    I'm working on getting your test data - reducing the dataset size for you.
     
    Thank you for your help.