cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mehaboob557 Member
Member

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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.
12 REPLIES 12
anandav Established Member
Established Member

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.

mehaboob557 Member
Member

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

mehaboob557 Member
Member

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

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

 

Can u please help me how to achieve this.

anandav Established Member
Established Member

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.

anandav Established Member
Established Member

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.

anandav Established Member
Established Member

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

anandav Established Member
Established Member

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)

 

mehaboob557 Member
Member

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
Community Support Team

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.