Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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 ) )
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.
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.
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
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
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
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 ) )
Hi @v-chuncz-msft,
I tried your dax function. I am getting the below error.
Please help me how can i overcome
I meant a calculated table.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |