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 new to PowerBI and i need help from experts like you..
I am connecting MySQL db and geeting the data.
I am attaching sample data of csv here which was exported from table visual.
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. i.e each quotations can have multiple versions from 0.1 to 2.1
My desired table should be,
1. I want to show only those quoations which have version 1.0 within version (0.1 to <=1.0) version
2. I want to show only those quoatations which have version 2.0 within version (1.1 to <=2.0) version
Sample output:
1. I want to show the quoations which have version 1.0 within version (0.1 to <=1.0) version
Quation# | min version | max version| duration
quote1 0.1 1.0 10 days
quote3 0.3 1.0 3 days
=================================================================================
2. I want to show the quoatations which have version 2.0 within version (1.1 to <=2.0) version
Quation# | min version | max version| duration
quote2 1.1 2.0 4 days
quote3 1.3 2.0 8 days
Please share pbix file if you get the output.
Please help me to achieve this. Thanks a ton in advance
Solved! Go to Solution.
Hi @mehaboob557,
I have look at your data and made the following PBIX file this is based on the following steps:
Make a group based on the version:
This can also be made using DAX but is much easier like this (if you want I can send you the DAX formula).
Create the following Measure:
Duration = DATEDIFF ( CALCULATE ( MIN ( 'sample'[businessDate] ), ALLSELECTED ( 'sample'[businessDate] ) ), CALCULATE ( MAX ( 'sample'[businessDate] ), ALLSELECTED ( 'sample'[businessDate] ) ), DAY )
Add in a table visual the following fields:
QuoteNo - Don't summarize
Version - First version (rename to min version)
Version - Last version (rename to max version)
Duration
versions(group) - reduce the size of the column until is hidden
Final version:
Add this measure to your table and the add it to the filters on the table result is as presented:
Validation = IF ( MAX ( 'sample'[version] ) = INT ( MAX ( 'sample'[version] ) ) = TRUE (), 1, 0 )
Result below:
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
Based on your sample data, I think you can directly create a calculated table like below:
Table = SUMMARIZE ( 'sample', 'sample'[quoteNo], "min version", MIN ( 'sample'[version] ), "max version", CEILING ( MAX ( 'sample'[version] ), 1.0 ), "duration", 1 * ( MAX ( 'sample'[businessDate] ) - MIN ( 'sample'[businessDate] ) ) )
Then you can put max column into a slicer for filtering expectd data.
Hi @v-sihou-msft ,
Thank you so much for your response with pbix file.
Everything is fine but, only change is,
I should show only those quotations which have version 1.0 within (0.1 to 1.0) and show only those quotations which have for have 2.0within (1.1 to 2.0). If quotations doesn't have 1.0 and versions 2.0, i should not display them
I wrote a minimal type in calculated column formula. This is the updated one: https://www.dropbox.com/s/hz4liavjn1xn9kt/min%20max%20dates%20and%20versions.pbix?dl=0
Proud to be a Super User!
Hi @mehaboob557
I created a calculated column and several measurs. Please follow this link for the solution: https://www.dropbox.com/s/hz4liavjn1xn9kt/min%20max%20dates%20and%20versions.pbix?dl=0
Proud to be a Super User!
Hi @danextian,
Thank you so much for your response with pbix file.
Everything is fine but, only two changes i need,
The versions within 0.1 to 1.0 versions, if the quoteNo has maximum version as 1.0 , then its max date should be shown and the duration should calculate on that.
2. I should show only those quotations which have version 1.0 within (0.1 to 1.0) and same for versions(1.0 to 2.0), quoteNo should have 2.0. or else we should not show other quotations.
In the above image, you can see that this quotation number doesn't have version 1.0. Hence, we should not show quotations which doesn't have 1.0 version (within 1.0 to 1.0 versions) and which doesn't have 2.0 version(within 1.1 to 2.0 versions)
i mean, I should show only those quotations which have version 1.0 within (0.1 to 1.0) and show only those quotations which have for have 2.0within (1.1 to 2.0). If quotations doesn't have 1.0 and versions 2.0, i should not display them
Hi @mehaboob557
My additional step would be to concatenate the version group and quoteno and then point the ALL and ALLECXEPT functionst to that column.
So for Max versionchange it from
Max Version = IF ( HASONEVALUE ( 'sample'[quoteNo] ), MAXX ( DISTINCT ( 'sample'[VersionGroup] ), CALCULATE ( MAX ( 'sample'[version] ), ALLEXCEPT ( 'sample', 'sample'[VersionGroup] ) ) ) )
to
Max Version = IF ( HASONEVALUE ( 'sample'[quoteNo] ), MAXX ( DISTINCT ( 'sample'[VersionGroup] ), CALCULATE ( MAX ( 'sample'[version] ), ALLEXCEPT ( 'sample', 'sample'[VersionGroup] ) ) ) )
I also updated the dropbox pbix.
Proud to be a Super User!
Thank you @danextian.
But, i need only those quotations to be displayed which have version 1.0 (within 0.1 to 1.0)
version group (0.1 to 1.0) i should not display because it doesn't have 1.0 version
version group (1.1 to 2.0) i should not display because it doesn't have 2.0 version
Ex:
1. Quote1 have version 0.1, 0.9, 1.8,2.0
quote 1 will display in version group (1.1 to 2.0) because it have 2.0 version
quote 1 will not display in version group (0.1 to 1.0) because it doesn't have 1.0 version
1. Quote2 have version 0.1, 0.9,1.0, 1.8,2.0
quote 1 will display in version group (1.1 to 2.0) because it have 2.0 version
quote 1 will display in version group (0.1 to 1.0) because it have 1.0 version
Hi @mehaboob557,
I have look at your data and made the following PBIX file this is based on the following steps:
Make a group based on the version:
This can also be made using DAX but is much easier like this (if you want I can send you the DAX formula).
Create the following Measure:
Duration = DATEDIFF ( CALCULATE ( MIN ( 'sample'[businessDate] ), ALLSELECTED ( 'sample'[businessDate] ) ), CALCULATE ( MAX ( 'sample'[businessDate] ), ALLSELECTED ( 'sample'[businessDate] ) ), DAY )
Add in a table visual the following fields:
QuoteNo - Don't summarize
Version - First version (rename to min version)
Version - Last version (rename to max version)
Duration
versions(group) - reduce the size of the column until is hidden
Final version:
Add this measure to your table and the add it to the filters on the table result is as presented:
Validation = IF ( MAX ( 'sample'[version] ) = INT ( MAX ( 'sample'[version] ) ) = TRUE (), 1, 0 )
Result below:
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Portuguêsthe second formula should have been
Max Version = IF ( HASONEVALUE ( 'sample'[quoteNo] ), MAXX ( DISTINCT ( 'sample'[VersionGroup and QuoteNo] ), CALCULATE ( MAX ( 'sample'[version] ), ALLEXCEPT ( 'sample', 'sample'[VersionGroup and QuoteNo] ) ) ) )
Proud to be a Super User!
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 |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |