cancel
Showing results for
Did you mean:

Most Recent
Super User

## [PowerBi][UX] Messages explaining visuals - Overlap technique

I know this practice for a long time, and I haven't used a lot until a month ago when a user helped me understand the importance of explaining the blank for their experience. Usually when I use to had blank visual, I didn't do anything about it because I was assuming it was obvious. If you filter data and the visual stays blank, it's because you don't have something the see there.

It sounds obvious but many people not related with Bi tools assume there is a failure or issue with the development, and they don't think about a data issue. That's one of many reasons why we need to know this practice of showing a message explaining different situations when that happen. Let's help the users experience and issues perceptions. At the end of the post, we might have even more ideas to develop.

Community Support

## Define a temporary function or multiple variables in a single column

Define a temporary function or multiple variables in a single column

Community Support

## Comparison of the text string with multiple elements

Comparison of the text string with multiple elements

Super User

## Last Mon, Tue, Wed, Thu, Fri, Sat and Sun of the Month in DAX

This article gives DAX formulas to calculate Last Mon, Tue, Wed, Thu, Fri, Sat and Sun of the Month.

Super User

## STARTOFWEEK and ENDOFWEEK functions in DAX

This article offers formulas to simulate STARTOFWEEK and ENDOFWEEK functions like Date.StartOfWeek and Date.EndOfWeek functions of Power Query language M

Community Support

## Group your data according to dynamic measure results

Group your data according to dynamic measure results

Community Support

## How to calculate about Matrix subtotal

Community Support

## Understand the Filter Context and How to Control it

Understand the Filter Context and How to Control it

Community Support

## Tips for handling common scenarios of employee attendance sheets

Tips for handling common scenarios of employee attendance sheets

Community Support

## Randomly filter with RAND or SAMPLE functions

Scenario

Sometimes, we need to pick some data as a sample to measure the overall situation randomly. For example, a factory produces multiple products, and each product needs to pick out a certain amount of samples to measure the overall pass rate. Moreover, in the selection process, it is also necessary to exclude the factor of human choice. So, how to use Power BI to realize this requirement?

Next, let us take a concrete look at different scenarios and needs.

Note:

In this blog, we only discuss how to sample, not the subsequent process of judging whether it is qualified or not.

Here is a sample table: Table1. And you can find others in the attached .xlsx file.

 Product Product ID Production Date Metrics Product A 1001 2022/3/12 1.37 Product A 1002 2022/3/12 1.69 Product A 1003 2022/3/13 2.23 Product A 1004 2022/3/14 1.98 Product B 2001 2022/3/12 2.01 Product B 2002 2022/3/13 3.04 Product B 2003 2022/3/13 2.65 Product B 2004 2022/3/14 1.75 Product B 2005 2022/3/14 2.23 Product C 3001 2022/3/13 4.11 Product C 3002 2022/3/13 3.51 Product C 3003 2022/3/13 3.66 Product C 3004 2022/3/14 2.35 Product C 3005 2022/3/15 4.01 Product C 3006 2022/3/16 2.98 … … … …   Part1: Randomly select a few rows of records from the entire table In Power BI, there is a DAX function named SAMPLE. It will return a sample of N rows from the specified table. And if OrderBy arguments are pro... Here are examples: EX1: Return a calculated table in which 3 samples are randomly selected from Table1. Table1_Sample_1 = SAMPLE (     3,                  /* The number of rows to return. */      Table1,         /* Any DAX expression that returns a table of data from where to extract the 'n' sample rows. */      1                  /* Since the minimum argument count for SAMPLE function is 3, put any scalar DAX expression. You can also specify a column. */      ) EX2: Return a calculated table in which 3 samples are randomly selected from Table1 and sorted by one specific column. Table1_Sample_2 = SAMPLE (     3,      Table1,      [Metrics]         /* Sort by [Value] column. */      ) In addition, from my limited testing, I find that SAMPLE function will not update the result returned after refresh if data has not been updated. Here is the test result: Then how do we get different results after each refresh? There are two methods. Table1_RAND = SUMMARIZE (     TOPN ( 3, ADDCOLUMNS ( Table1, "Rand", RAND () ), [Rand] ),     [Product],     [Product ID],     [Production Date],     [Metrics] ) b. Create a calculated column in the original table and just return the filtered values in Table visual. Note: To avoid affecting the test results above, we copy the Table1 below, generate Table2, and then create a new computed column in Table2. Rand = RAND() We can see that after each refresh, the returned rows are different.   Now, we know how to get sample rows from one table. Then, how do we get the same number of rows for each category? Let’s go to the next part.   Part2: Randomly select records with the same number of rows by category from the entire table a. Based on my test, class-by-class sampling cannot be achieved by SAMPLE function in this scenario. For example: Table1_Sample_3 = VAR ProductNum_ =     DISTINCTCOUNT ( Table1[Product] ) RETURN     SAMPLE ( ProductNum_ * 2, Table1, [Product] ) b. Then we use RANK function to create measures based on the Rand column in Part1 and then use “Filters on this visual” feature.Rank_Table2 = RANKX (     ALLEXCEPT ( Table2, Table2[Category] ),     CALCULATE ( SUM ( Table2[Rand] ) ),     ,     DESC,     DENSE ) So far, we have implemented sampling by category. Then, if some categories have fewer records and some categories have more records. Then how do we sample it? Let’s go to Part3.   Part3: Randomly select records with different rows by category from the entire table  It is, in fact, very simple that we only need to combine the SWITCH function and add a judgment condition and then use “Filters on this visual” feature. For exam... RankFilter_Table2 =VAR Count_ =    CALCULATE ( COUNTROWS ( Table2 ), ALLEXCEPT ( Table2, Table2[Product] ) )VAR Threshold_ =    SWITCH ( TRUE (), Count_ >= 8, 4, Count_ >= 6, 3, Count_ >= 4, 2, 1 )RETURN    IF ( [Rank_Table2] <= Threshold_, 1 )   If you want to get more details, please check the attached .pbix file. Hope this blog can help you.     Author: Icey Zhang Reviewer: Kerry Wang & Ula Huang

Super User

## [DAX] Why and how to use variables

I have been giving support for DAX in different places. Something that is becoming more frequent are long DAX functions repeating code (they might be format, but long). Usually, these problems arrive when there is a performance issue that results in a long measure.

In this article I’m going to explain with my words the importance of using variables in our code. We should always consider using variables because they have several benefits. Some people might think they only help to make the code feel cleaner, but I can assure you it will help on maintainability too.

Community Support

## How to find values from another table

In this article, we will talk about how to look up values in either original table or another table. Now we will show you some examples to help you better understand it.

Community Support

## How to rank values from different tables with DAX code

The Power BI DAX functions enable you to process data from multiple tables. Some users want to obtain some data from a filtered table, then use these data to find related items in another table, and finally perform a series of operations on them.
In this article, I will mainly introduce 2 parts. The first part introduces several ways to get related items from other tables. And the second part realizes the ranking of different tables based on the principles of scenario 1.

Community Support

## How to use the slicer on the column

Some users want to filter and summarize based on column names, however, some difficulties arise due to the fact that dax statements basically operate on the data in the specified column.

For example, there is a report card with only names and test takers' scores for each subject, the school wants to have a report that can be filtered using a slicer for each subject's score, how can this be achieved?

Community Support

## Tips on displaying icons based on field values in Power BI

When exploring and analyzing data, we may need to add colored icons to help us visually identify patterns and trends. Currently, there are two main ways to add icons.
1. Icon sets for conditional formatting
a) built-in icon sets
b) custom icon sets (json file of theme)
2. Conditional fields
a) SVG xml, Img URL, Built-in icon name
b) Html-code of Unicode characters