Forum Discussion

mehaboob557's avatar
mehaboob557
Icon for Resolver III rankResolver III
9 years ago
Solved

Help needed to get data in between 2 values with where conditon in DAX

Hi All,

 

I am stuck to get data bewteen two values but it has to be qulaified with one particular value.

 

To be in detail,

 

I have versions from 0.1 to 2.0(i.e 0.1, 0.2, 0.3 ..... 0.9, 1.0, 1.1, .... 2.0) for each quotations. It means, each quotations will have multiple versions as shown below.

 

Quote# |  version

quote1    0.1

quote1    0.3

quote1    1.0 

quote1    1.5

quote1    2.0

quote2    0.2

quote2    0.9

quote3    0.3

quote3    1.0

 

I want to display only quote1 and quote3 all versions upto 1.0 except quote2 because, it doesn't have 1.0 version

 

My scenario, to display only those quotations with the versions from 0.1 to 1.0 where quotation should have 1.0 version . If any quotation which doesn't have 1.0 version, i should exclude that.

 

please help em how can i write DAX function to get this.

 

I tried by creating a coulumn with

"Bucket = IF('prod_check dw_proposal'[version] >= 0.1, 'prod_check dw_proposal'[version] < 1.1)"

 

But, it is displaying all the quotations without checking 1.0 is there or not for each version

 

Please suggest me any DAX function or any other related measure to achieve my scenario

 

Thanks a lot in advance.

 

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-chuncz-msft's avatar
    v-chuncz-msft
    9 years ago

    mehaboob557,

     

    You may refer to the following DAX that creates a new table.

    Table =
ADDCOLUMNS (
    GENERATE (
        FILTER ( Table1, Table1[version] = 1 ),
        SELECTCOLUMNS (
            TOPN (
                1,
                FILTER ( Table1, Table1[quoteNo] = EARLIER ( Table1[quoteNo] ) ),
                Table1[version], ASC
            ),
            "min version", Table1[version],
            "min date", Table1[businessDate]
        )
    ),
    "duration", DATEDIFF ( [min date], Table1[businessDate], DAY )
)

12 Replies

  • anandav's avatar
    anandav
    Icon for Skilled Sharer rankSkilled Sharer
    9 years ago

    Hi mehaboob557,

     

    If you want to only "display" version 1.0 quotes in a visual then it can be easily done by using the 'Filters' section/settings of the visual.

     

    As you can see in the original table (if left) it has all the quotes. In the table that is selected (in the right) it displays only the quotes with version 1.0.

    Steps:

    Select the visual (in this case the table).

    As indicated in the image in the 'Filters' section, under 'Visual Level Filters' set it to show only version 1.0

     

    Hope this helps.

    • mehaboob557's avatar
      mehaboob557
      Icon for Resolver III rankResolver III
      9 years ago

      Hi anandav,

       

      Thank you I can do that. But, my scenario is different.

       

      I want to show all quoations which have qualified to version 1.0

       

      my output should be ,

       

      Quation#  |  version

      quote1       0.1

      quote1       0.3

      quote1       1.0 

       

      quote3       0.3

      quote3       1.0

       

      excluding 

      quote1    1.5

      quote1    2.0

      quote2    0.2

      quote2    0.9

       

      If i select in filter , it will only show

       

      Quation#  |  version

       

      quote1       1.0 

       

      quote3       1.0

       

      But i want to show like below

       

      Quation#  |  version

      quote1       0.1

      quote1       0.3

      quote1       1.0 

      quote3       0.3

      quote3       1.0