Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mehaboob557
Resolver IV
Resolver IV

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

@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

12 REPLIES 12
anandav
Skilled Sharer
Skilled Sharer

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.

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

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.

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

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 😞

 

Thanks a ton in advance

 

Anonymous
Not applicable

Hi Mehaboob,

 

Here are the step I followed:

 

Step1: Take the quateNo where version=1.0 and make a new file

Step 2: Import sample.csv and new file with quateNo where version=1.0

Step 3: Make relationship between these two files 1to many

Step 4: create a table with required column and use quoteno from second file.

Step 5: put visual filter where version is less than 1.1

 

Here is link to pbxi file.

 

Regards,

Kundan

@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.

Hi @v-chuncz-msft,

 

I tried your dax function. I am getting the below error.

 

eroor.PNG

 

Please help me how can i overcome

@mehaboob557,

 

I meant a calculated table.

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.

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)

 

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

Refere to DAX EARLIER function reference.

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

 

Can u please help me how to achieve this.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.