Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

dynamic measure value based on date selection in slicer

  • 1.  dynamic measure value based on date selection in slicer

    Silver Contributor
    Posted 26 days ago
    Hi all,

    i have condition if selected slicer date is >= 01-21-2020 then my measure shound count the data from list of room1 table else
    if selected slicer date is < 01-21-2020 then my measure shound cound the data from list of room 2 table

    how can i do this in dax

    ------------------------------
    Ganesh Kumar
    Analyst
    ------------------------------
    Conference-PBI_200x200


  • 2.  RE: dynamic measure value based on date selection in slicer

    Silver Contributor
    Posted 26 days ago
      |   view attached
    Hi All atatched the sample file for your reference ..  please take any one of the date to segerate the two table.

    ------------------------------
    Ganesh Kumar
    Analyst
    ------------------------------

    Attachment(s)

    pbix
    test 123.pbix   153K 1 version
    Conference-PBI_200x200


  • 3.  RE: dynamic measure value based on date selection in slicer

    Silver Contributor
    Posted 24 days ago
      |   view attached
    Hi @Ganesh Kumar,

    The Column Date in table Daily Data has several rows for the same date, because it's a calculate column from other column datetime format.
    Changing the DAX measure to this bellow will work fine.

    MeasureSee also the pbix updated
    Let me know if it suits for you.

    Thanks,


    ------------------------------
    José Pintor
    Power BI Consultant / Project Manager
    Oliveira de Azeméis - Portugal
    910826215
    ------------------------------

    Attachment(s)

    pbix
    test 123.pbix   155K 1 version
    Conference-PBI_200x200


  • 4.  RE: dynamic measure value based on date selection in slicer

    Gold Contributor
    Posted 22 days ago
      |   view attached
    Hi Ganesh & José,

    If I may, I would like to add my point of view.

    José is right in his answer... VALUES() instead of SELECTEDVALUE() makes the result come out. But in you case SELECTEDVALUE() is the right statement to use because it returns one single value while VALUES() can return many values if many dates are selected in the slicer. Not correct here

    To do so you have to change a little bit your data model and keep in mind few things
    - When handling dates, always create a Calendar table
    - 1 Business entity = 1 table
    - Star schema is the Top Model ;)
    - Avoid Bidirectional connections and many to many relationships

    In the file attached, I appended the 2 List of stockroom adding a status column set to "OLD" or "Current"
    I created a calendar table and connected the two tables  with the date field and rebuilt the measures...

    I hope this point of view will help... feed backs welcome

    ------------------------------
    Nicolas MENDEZ
    Data & BI consultant
    Becom Consulting
    ------------------------------

    Attachment(s)

    pbix
    test 123.pbix   166K 1 version
    Conference-PBI_200x200


  • 5.  RE: dynamic measure value based on date selection in slicer

    Silver Contributor
    Posted 22 days ago
    Edited by José Pintor 22 days ago
    HI @Ganesh Kumar & @Nicolas MENDEZ​​,
    Like Nicolas said ist true that if you don't select any date return an error.
    With this control (HASONEVALUE ) the DAX measure will work anyway using SLECTEDVALUE or VALUES...
    MeasureYou can even configure the slicer to force allways only one value select...
    If you want a more flexible way (ex. select 2 or more dates) and get the result combined here the DAX measure
    Measure2

    Thanks,

    ------------------------------
    José Pintor
    Power BI Consultant / Project Manager
    Oliveira de Azeméis - Portugal
    910826215
    ------------------------------

    Conference-PBI_200x200


  • 6.  RE: dynamic measure value based on date selection in slicer

    Silver Contributor
    Posted 22 days ago
      |   view attached
    Hi @Nicolas MENDEZ & @Jose Pintor

    thanks for the help ,, yes selectedvalue is the best workaround for my requirement . ​​​​​​
    this is how i tried my self and got the out put. but i dont know this is correct way to get the output.
    IF(
    SELECTEDVALUE('Daily Data'[Date])<DATE(2020,01,23),
    CALCULATE(COUNT('List of stockroom'[Week Day]),'List of stockroom'[Status]="old"),
    CALCULATE(COUNT('List of stockroom'[Week Day]),'List of stockroom'[Status]="New")
    )

    i have a on more question 

    i like to compare the daily data to list of stockrroms . attached my output format for your reference.
    so i want the visual like attached file "A column and C column  is the list of stockroom" , "B and D is the daily data qnique values for the day in daily data.
    how can get the output like this format in power bi 


    ------------------------------
    Ganesh Kumar
    Analyst
    ------------------------------

    Attachment(s)

    xlsx
    test output data.xlsx   13K 1 version
    Conference-PBI_200x200