Reply
Member
Posts: 117
Registered: ‎08-01-2017
Accepted Solution

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.

 

 


Accepted Solutions
Community Support Team
Posts: 4,198
Registered: ‎07-09-2016

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

@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 )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post


All Replies
Established Member
Posts: 173
Registered: ‎01-11-2017

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

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.

Img1.jpg

 

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.

Member
Posts: 117
Registered: ‎08-01-2017

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

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

Member
Posts: 117
Registered: ‎08-01-2017

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

[ Edited ]

Hi @v-shex-msft / @v-sihou-msft,

 

Can u please help me how to achieve this.

Established Member
Posts: 173
Registered: ‎01-11-2017

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

Hi @mehaboob557,

 

Create a new column as:

Max Version = CALCULATE(MAX(Sheet1[Version]), FILTER(Sheet1, EARLIER(Sheet1[Quote]) = Sheet1[Quote]))

 

Then in the visual you can filter by Max Version is 1.

 

MaxVersion.png

 

Hope this helps.

Established Member
Posts: 173
Registered: ‎01-11-2017

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

BTW, using EARLIER has some performance issue if you have large number of rows.

Refere to DAX EARLIER function reference.

Established Member
Posts: 173
Registered: ‎01-11-2017

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

Sorry, the above will not work if you have version more than 1.

So try this:

Version1_0 = If(Sheet1[Version] = 1, CALCULATE(COUNT(Sheet1[Version]), FILTER(VALUES(Sheet1[Version]), EARLIER(Sheet1[Quote]) = Sheet1[Quote])), 0)

 

Then filter for value Verion1_0 > 0 which will display the quotes with version 1.0

 

MaxVersion.png

Established Member
Posts: 173
Registered: ‎01-11-2017

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

You can get the same results without using EARLIER.

Version1_0 = If(Sheet1[Version] = 1, CALCULATE(COUNT(Sheet1[Version]), FILTER(VALUES(Sheet1[Version]), Sheet1[Quote] = Sheet1[Quote])), 0)

 

Member
Posts: 117
Registered: ‎08-01-2017

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

Hi @anandav

 

I am unable to get what i want.

 

I will explain what my functionality actually.

 

the output would be quoations having version 1.0, i want min version of that quotation and maximum version would be 1.0.

 

Minimun version can be 0.1 to 0.9 for each particular quoation.

 

Then i have to find min version date and max version date and duration between minmum version and version 1 of each quotation.

 

Output:

 

Quation#  |  min version | max version|  duration

 

quote1       0.1                        1.0              10 days

 

quote3       0.3                        1.0                 3 days

 

 

Can u please help. i am attaching sample data of csv here.

 

Please share pbix file if you get the output.

 

I am stuck in this from past two days Smiley Sad

 

Thanks a ton in advance

 

Community Support Team
Posts: 4,198
Registered: ‎07-09-2016

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

@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 )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.